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.