Sum of AMount using Max version Logic in Qlikview - Expression

Ankhi — Tue, 02 Nov 2021 13:54:16 GMT

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

topic Sum of AMount using Max version Logic in Qlikview - Expression in App Development

Sum of AMount using Max version Logic in Qlikview - Expression