Data Selection Based on Max Value

Ankhi — Thu, 08 Jul 2021 05:37:12 GMT

I have a data like below, this same pattern is repeated for multiple LeaseIDs with different Periods. For some cases , like Mar-20 here, there are two lines against the same Type = Cash. In such cases I would only like to pick up the ones with maximum header id i.e Mar-20 , LeaseID=L1 ,Type= Cash should have 150 only .

Can you please help me create a set analysis expression or load script in Qlikview which will only load filtered data based on max header id as well as Period, Lease Id and Type and Amt.

Header Id Line Id LeaseID Type Period Amt
100 11 L1 Cash Jun-21 100
101 12 L1 Amort May-21 200
102 13 L1 Interest Apr-21 300
100 14 L1 Cash Jun-20 100
101 15 L1 Amort May-20 200
102 16 L1 Interest Apr-20 300
100 17 L1 Cash Mar-20 250
120 18 L1 Cash Mar-20 150
101 19 L1 Amort Mar-20 500
102 20 L1 Interest Mar-20 430

103 21 L2 ..... ... ...

I am trying to use the below expression

MAX(AGGR(Sum(Amt),LEASE_ID,HEADER_ID,Period,Type))

but for Mar -20 and Lease Id = L1 and Type = Cash it is picking up 250 instead of 150.

Can you please help?

Many Thanks

Ankhi

topic Data Selection Based on Max Value in QlikView

Data Selection Based on Max Value