How to add a filter for “yesterday” on a DATS field coming from BW in a HANA Calculation View

Share Button

How to add a filter for “yesterday” on a DATS field coming from BW in a HANA Calculation View

I searched several SCN threads, for example this one Need help to figure out simple date filter in calculation view but they did not help me. Some recommend to create a calculated column that is in SQL Date format and then apply a filter. This forces the conversion of all records prior to applying the filter so it can affect performance. Others recommend a join with M_TIME_DIMENSION, so I thought it would be helpful to show the filter I applied and learn from your comments about the pros and cons of this approach.

I just need to select from a table (a fact table in this case) all records with a date field with the date corresponding to yesterday. As the field for the calendar day in the fact table is SID_0CALDAY, an integer, I do an integer conversion to the expression, but if what you have is a VARCHAR as in any converted DATS, exclude the starting INT( as well as the last parenthesis on the expression.

What worked for me was:

1
2
3
INT(string(component(adddays(now(), -1),1)) +
rightstr('00' + string(component(adddays(now(), -1),2)),2) +
rightstr('00' + string(component(adddays(now(), -1),3)),2))

The ‘component function returns the year, month and date if you pass 1, 2 or 3 as second parameter, and the rightstr function is to pad zeros in months or days between 1 and 9.

Share Button