Need help to figure out simple date filter in calculation view

[Former Member]

Guys and Gals,

I have a simple issue that I can't wrap my little brain around.

I need a simple date filter where I get data for only yesterday. For example ERDAT = yesterday.

This has to be done in the expression editor in the first projection node, I want to force this filter on the attribute view so the performance will be great. Putting a filter in the first projection node where you consume the Attribute view is equivalent to putting that filter directly in the Attribute view (please correct me if I'm wrong but I'm 99% sure and tested it).

So my basic filter in the expression editor will be:

ERDAT = now() -1

Sounds simple enough but doesn't it? Can someone please tell me the syntax where I only get yesterday's date?

Note: If the answer is create a calculated column, etc., then I am not enforcing the filter on the attribute view right away.

Thanks alot experts.

[Former Member]

Hey,

I actually want to convert now() to NVARCHAR. Can someone tell me how I can do this in the expression editor or even calculated column?

I need to filter like this BUDAT = adddays(now(), -1)

Also, the hyphens in the date are giving problems, I need to remove those.

Right now I am converting BUDAT to date, but I want to convert now() to NVARCHAR so the sysem doesn't have to go through all the records to convert BUDAT to date.

[lbreddemann]

Go and try dats_from_daydate ( adddays(now(), -1) ) ...

Remember, it's not so much about getting the date into (N)VARCHAR but into the SAP specific DATS format.

- Lars

[Former Member]

Hello Lars,

We had a similar requirement to get data from a table where VALIDFROM is yesterday.

Until came across your post was using a calculated column "VALIDFROM_NEXT_DAY" as adddays(date("VALIDFROM"),1) and then filtering that column as "VALIDFROM_NEXT_DAY" = daydate(now())

Thanks for your help.

[Former Member]

Well, what I expected is happening. I have a bad performance. This is frusturating.

I just need data from yesterday. So I did the below in the first node in the calculation view:

date(BUDAT) = adddays(now(), -1))

^This works but the problem is that HANA has to convert BUDAT for all the records before it can do this filter = which is causing performance issues.

Can anyone tell me an optimal way to get data from yesterday in the calculation view?

[lbreddemann]

Why don't you convert your expression result to the DATS format first?

Or, depending on your HANA version you may process the DATS format directly with dats_add_days.

- Lars

[Former Member]

Hey guys,

The syntax is no working, I get an error.

I'm doing this in the expression filter in the projection node. If you notice there is limited syntax.

I just need data where ERDAT is yesterday. I want to improve the performance where I only retrieve yesterday's data, so the filter must be enforced right away when attribute view runs.

I don't want to get all the data THEN use this filter, that defeats the purpose.

Please help =/

[Former Member]

Try ADD_DAYS(CURRENT_DATE,-1)

[lbreddemann]

There had been quite a lot of discussions here on SCN around date arithmetic.

You may want to look into the add_days() function and use a negative number of days to add to the day today to retrieve yesterday's date.

- Lars