Aggr to get max value

[tomovangel]

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

[Anil_Babu_Samineni]

What are you getting when you use this?

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

[tomovangel]

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

[patroser]

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?

[Anil_Babu_Samineni · Accepted solution]

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

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

[tomovangel]

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

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

[tomovangel]

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

[tomovangel]

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

[Anil_Babu_Samineni]

Awesome