Skip to Content
avatar image
Former Member

How to model last 10 working days using incremental counter in HANA Graphical Calculation View?

Hi All,

One of my requirement requires me to get a list of days for last 10 working days(working days calendar been provided by Business),

My Approach: The way I was trying to achieve by creating an incremental counter starting from the current day but I am unable to model it in the graphical calculation view, any suggestion is welcome, below screenshot to explain the logic.

Note: If it is not possible to achieve the same in Graphical calculation view, can someone help me with the code for creating the same in SQL scripted view?

Thanks in advance for any suggestion.

Regards,

Abhi

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • May 12 at 04:49 PM

    You have to use advanced sql , with partition by Year and Order By Date

    SUM(WORKING_DAY) OVER (PARTITION BY YEAR ORDER BY DATE DESC) RunningDay

    I wrote SQL which works well with M_TIME_DIMENSION View generated in SAP HANA Modeler

    SELECT SUM(WORKING_DAY) OVER (PARTITION BY YEAR ORDER BY DATE_SQL DESC) RunningDay ,* FROM ( SELECT CASE WHEN DAY_OF_WEEK IN ('06','05') THEN 0 ELSE 1 END WORKING_DAY, * FROM "_SYS_BI".M_TIME_DIMENSION WHERE DATE_SAP <='20180513' ORDER BY DATE_SAP DESC ) ORDER BY DATE_SAP DESC



    Add comment
    10|10000 characters needed characters exceeded

  • Nov 21 at 10:09 AM

    Hi Abhi,

    not sure whether I understand your request fully, because I had expected that you don't want to see e.g., May 13 in your list as it is no working day and not in your inclusion criteria. Nevertheless, the general approach should work. Filter e.g., in a projection for working days and add a rank node on top that returns only 10 values sorted by date:

    This would return:

    Best,

    Jan

    Add comment
    10|10000 characters needed characters exceeded