topic Re: Aggr max function in QlikView

Aggr max function

Thu, 07 Mar 2013 19:42:38 GMT

Hi guys

I am wating to find the max valueof sales for a particular company.

Have tried this but not working.

=if([Total Sales]=max(Aggr([Total Sales],[Company Name])),'Y', 'N')

Re: Aggr max function

Gysbert_Wassenaar — Thu, 07 Mar 2013 19:52:43 GMT

What are you trying to do? Doesn't a straight chart with Company Name as dimenions and max([Total Sales]) as expression give you the maximum total sales for the companies?

Re: Aggr max function

Thu, 07 Mar 2013 23:23:56 GMT

Apologies- I should have been more clear.

I have :

Company Total Sales

Company A 20

Company A 30

Company A 40

Company B 10

Company C 32

I want the table to only show me the highest values for each company.

My idea was to create another dimension which would flag the highest value with a 'Y'.

Is there a better way to go about this?

Re: Aggr max function

Thu, 07 Mar 2013 23:42:40 GMT

if you have a straight table with two dimensions:

Company,

[Total Sales]

then expression you are looking for is:

if([Total Sales]=aggr(nodistinct max([Total Sales]),Company),'Y','N')

Best

Karol

Re: Aggr max function

Thu, 07 Mar 2013 23:56:41 GMT

This is brilliant- works perfectly. Thank you so much.

No that I have a column flagging the highest value - how can I have my table only showing rows with Y's (ie the max values)? And is it possible to hide the Y/N (flagging) column?

Thanks again for your help

Re: Aggr max function

Fri, 08 Mar 2013 08:32:07 GMT

bump