topic Help with Max and AGGR Function in QlikView

Help with Max and AGGR Function

Ankhi — Thu, 08 Jul 2021 05:44:18 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.

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

Re: Help with Max and AGGR Function

rubenmarin — Thu, 08 Jul 2021 07:27:48 GMT

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)

Re: Help with Max and AGGR Function

Ankhi — Thu, 08 Jul 2021 09:17:48 GMT

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.

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)

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

Re: Help with Max and AGGR Function

rubenmarin — Thu, 08 Jul 2021 10:29:00 GMT

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.

Re: Help with Max and AGGR Function

Ankhi — Mon, 12 Jul 2021 09:44:16 GMT

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