topic Re: Aggr to get max value in QlikView

Aggr to get max value

tomovangel — Wed, 25 Nov 2020 16:16:04 GMT

Hello guys, I have a StoreID,StoreType and NetAmount columns.

I want to show the max NetAmount in a single storeID, from a single type( StoreType = S,M,L)

I tried

=max(aggr(Sum(NetAmount),StoreType = 'M',StoreID)) & chr(13) & StoreID

And it returns only a value, not the StoreID, this value belongs to.

I also tried

=FirstSortedValue(StoreID , - aggr(sum(NetAmount),StoreType = 'L')) & chr(13) & aggr(sum(NetAmount),StoreType = 'L')
But it doesn't return anything.

Any suggestions will be highly appreciated,

Thanks

Re: Aggr to get max value

Anil_Babu_Samineni — Wed, 20 Sep 2017 07:48:32 GMT

What are you getting when you use this?

Max({<singletype = {'S','M','L'}>} Aggr(NetAmount, StoreID))

Re: Aggr to get max value

tomovangel — Wed, 20 Sep 2017 07:52:14 GMT

I am getting the maximum value for the selected period,
But I am not getting which store, is with that value

Re: Aggr to get max value

patroser — Wed, 20 Sep 2017 07:53:51 GMT

Hi,

I'm not quite sure if I understood what the expected output would be.
Could you provide the sample output which is on your mind?

Re: Aggr to get max value

Anil_Babu_Samineni — Wed, 20 Sep 2017 07:54:07 GMT

Then, Try this? If not, Remove - and check before Aggr()..

FirstSortedValue(StoreID, -Aggr(Sum({<singletype = {'S','M','L'}>} NetAmount), StoreID)

Re: Aggr to get max value

tomovangel — Wed, 20 Sep 2017 07:56:06 GMT

283092 - 1014
max( NetAmount ) - ( Store ID ) ( of type L)

48 293 - 1001
max(NetAmount)- ( StoreID) ( of type S)

Re: Aggr to get max value

tomovangel — Wed, 20 Sep 2017 07:58:35 GMT

This is returning the StoreID with the max value, from the chosen StoreType

I will test it for a sec, and will mark your answer correct if it works

Re: Aggr to get max value

tomovangel — Wed, 20 Sep 2017 08:00:25 GMT

It works when i make 2 textboxes
1 is with your expression and it gives me StoreID
2 is with =Max({<singletype = {'S','M','L'}>} Aggr(NetAmount, StoreID)) ( and it gives me the NetAmount)

Thanks

Re: Aggr to get max value

Anil_Babu_Samineni — Wed, 20 Sep 2017 08:01:05 GMT

Awesome