Skip to Content

HANA modeling - filter on last date

Hi,  I have got modeling requirement to filter out the last value based on certain column combination, the output of my caclulation view is as below,

EmployeeGroupCreated onExpense100021Corporate10 - Sep - 201520000 $100021Corporate09 - Oct - 201515000 $100021Travel06 - Jul - 201518000 $100021Corporate05 - Aug - 201619000 $100021Travel10 - Sep - 201624000 $

when I am modeling this data to next level in HANA studio, I would need to all only the latest records available on the combination of Employee, Group and date. In the above table, only the ones highlighted in red are required for reporting.  so for every combination of Employee and group, I will need to identify the last date record and allow that only further.

I am thinking is there any way, to achieve this requirement in graphical calculation view,  if not going to scripted calculation view is an option. But at this moment, I am waiting for other options.  if you have come across this requirement, please let me know.

Thanks

Sreekanth

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Sep 23, 2016 at 10:42 PM

    Hi Sreekanth,

    I would suggest try to use RANK Functionality. Please check below example.

    I have created similar table in my system.

    I have done simple modeling using RANK node.

    RANK NODE setting ;

    Final Output :

    - Vikram Divekar

    Add comment
    10|10000 characters needed characters exceeded

    • Thank you, It worked fine Vikram. if you have some time, I have got another requirement on this data model, however it is for another report, I will build a new model with out Rank node logic.  To explain the logic better, just changed the numbers in the expense column.

      RecordEmployeeGroupCreated onExpense1100021Corporate10 - Sep - 201520000 $2100021Corporate09 - Oct - 201515000 $3100021Travel06 - Jul - 201520000 $4100021Corporate05 - Aug - 201625000 $5100021Travel10 - Sep - 201624000 $

      Unlike the previous requirement, where only last value has to be shown, here all of the records to be shown however a new filter will be applied.

      user would like to review the employee who had a change in the expense of initial date and last date by group, in the above example record 1 is the initial record and record 4 is the last the increase is 5000,  user can input the variable, "Expense increment" = 5000, all the employee who met this condition along with group are to be shown in the report.

      In the above example, the data should be, records 1, 2 and 4 displayed.  but not 3 and 5, because in the travel, employee had a increase of expense +4000, it did not met the filter criteria.

      I am thinking a by creating a new calculated column and store the difference of 1st and last date records value for every combination of Employee and Group,  but not sure how do I achieve that.

      Please let me know, it is possible by graphical nodes?