HANA modeling - filter on last date

[ssurampally]

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,

Employee	Group	Created on	Expense
100021	Corporate	10 - Sep - 2015	20000 $
100021	Corporate	09 - Oct - 2015	15000 $
100021	Travel	06 - Jul - 2015	18000 $
100021	Corporate	05 - Aug - 2016	19000 $
100021	Travel	10 - Sep - 2016	24000 $

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

[former_member200930 · Accepted solution]

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

[ssurampally · Accepted solution]

It looks like, promising. I will apply this login on my model, I will let you know the result, Thank you so much for your assistance Vikram.

[ssurampally · Accepted solution]

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.

Record	Employee	Group	Created on	Expense
1	100021	Corporate	10 - Sep - 2015	20000 $
2	100021	Corporate	09 - Oct - 2015	15000 $
3	100021	Travel	06 - Jul - 2015	20000 $
4	100021	Corporate	05 - Aug - 2016	25000 $
5	100021	Travel	10 - Sep - 2016	24000 $

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?