Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Ankhi
Creator
Creator

Sum of AMount using Max version Logic in Qlikview - Expression

Hi 

I have to create the following logic in the expression not in the script in a straight / Pivot table.

1. Retrieve all the rows where CREATION_DATE is less than the User Selected date AS_DATED.
(Not shown in the sample data). AS_DATED is a filter in the sheet from where user can select a date.
2. Then for each LNUMBER, Select the distinct TERM IDs available.
3. For each distinct TERM_IDs then select the MAX Version. There may be multiple rows with max version.
4. Then SUM the amount.

In the example below the output should be the last 3 lines. Sum Of Amount should be (16.5+ (-74.93) + .54)=  - 57.89

ID LNumber Flag Version Term ID Created Date Type Short Date AMOUNT
2245 VU66PWY N 4481 2245 14-Jan-21 AMORTIZATION 01-04-2021 16.5
2245 VU66PWY N 5035 2245 14-Jan-21 AMORTIZATION 01-04-2021 16.5
2245 VU66PWY N 5035 78244 20-Apr-21 AMORTIZATION 30-04-2021 -74.93
2245 VU66PWY N 5035 2245 20-Apr-21 AMORTIZATION 30-04-2021 0.54

 

Can you please help?

 

Many Thanks

Ankhi

0 Replies