Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

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

Help with Max and AGGR Function

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.

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?

Ankhi_0-1625723009148.png

 

 

Many Thanks

Ankhi

Labels (2)
4 Replies
rubenmarin

Hi, Max returns the higher value, if you wantto sort by another field you can use FirstSortedValue:

- Aggr(FirstSortedValue(Amt, -HEADER_ID), LEASE_ID,Period,Type)

- or: FirstSortedValue(TOTAL <LEASE_ID,Period,Type> Amt, -HEADER_ID)

Ankhi
Creator
Creator
Author

Hi Ruben, 

Thanks for getting back to me. But this is not working. I will try to explain my scenario in a bit ore detail

My application looks like below.

Ankhi_0-1625735034150.png

The users can select any number of Periods as I have selected  and there are other filters as well.

This is the qvd data (below)

Ankhi_2-1625735531985.png

So for Type Amortization the application should pick the amount which has a header id of  127590.

and for the rest of the Types, since there is only one row so picks up whatever there is ..

This qvd is filtered on Lease Id and Period. There are multiple Lease ids with different Types for different periods.

In case for the same lease id  and same period, if there is a repetition of Types as my example above , then the  amount of the line with highest header id should be picked up. (not the highest amount)

Thanks

Ankhi

rubenmarin

Hi, fisrtsortedvalue returns the value of the highest header id (not the highest amount), if doesn't works it can be related with the dimensoion or the type of chart used.

Can you create a dummy document with a few inline data that demonstrates the issue? this way we can use that document to make it work.

Ankhi
Creator
Creator
Author

Hi Rubenmarin,

I have found out there are a lot of data issues at the source. We are waiting for the data to be fixed at the source as of now. Once done if this issue still persists then we definitely get back.

Thanks again for getting back to me .

Regards

Ankhi