Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
Ankhi
Creator
Creator

Data Selection based on max value of a column

Hi

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

 

 

Labels (1)
0 Replies