topic Return text from aggr() function in QlikView

Return text from aggr() function

Sun, 30 Oct 2011 14:47:41 GMT

Hello,

A little help will be much appreciated.

The following is my data set:

org, store nr, store name, wk, sales

a, 1, a1, 1, 100

a, 2, a2, 1, 110

a, 3, a3, 1, 120

a, 4, a4, 1, 125

b, 1, a1, 1, 99

b, 2, a2, 1, 10

b, 3, a3, 1, 130

b, 4, a4, 1, 109

a, 1, a1, 2, 126

a, 2, a2, 2, 127

a, 3, a3, 2, 135

a, 4, a4, 2, 10

b, 1, a1, 2, 190

b, 2, a2, 2, 179

b, 3, a3, 2, 110

b, 4, a4, 2, 111

I am using

=max(aggr(sum(sales),[store nr],org,wk)

to derived the highest sale -- 190 in this case

I can't seem to get the formual working to return the actual name of the store (a1 in this case) instead of the sales number. I tried maxstring() but no luck.

Thanks!

Return text from aggr() function

its_anandrjs — Sun, 30 Oct 2011 14:56:14 GMT

Hi,

Your formula should be like

=max(aggr(sum(sales),[store nr],org,wk))

which returns 190

Rgds

Anand

Return text from aggr() function

swuehl — Sun, 30 Oct 2011 14:59:13 GMT

This should you return the store name:

=concat(aggr(if(sum(sales)=max(total aggr(sum(sales),[store nr],org,wk)),[store name]),[store nr],org,wk),' / ')

I used concat in case you have more than one store with max sales.

Hope this helps,

Stefan

Return text from aggr() function

Sun, 30 Oct 2011 15:03:31 GMT

Hi Anand,

thanks for your reply but I already got that piece working.

Return text from aggr() function

Sun, 30 Oct 2011 15:06:28 GMT

Thanks Stefan,

This worked! Thesemight be silly questions but what is the difference between aggr() and total aggr(). Also, why my original formula wasn;t sufficient to return the name?

Many thanks!

Re: Return text from aggr() function

swuehl — Sun, 30 Oct 2011 15:36:23 GMT

You need the total because I needed to embed your original expression in another advanced aggregation, but I wanted the total max sales, not with respect to the outer aggr() dimensions. Not sure if I make myself clear, but if you leave out the total, you should get all store names returned, because the comparision is than done with max calculated in the outer dimensions context only (which will always return a true, then).

Your original expression was ok to calculate the maximum sales, but as soon as you apply max to the advanced aggregation, you will lose the more granular information (which store it belongs to) because of the aggregation.

I was thinking about some other solutions like using FirstSortedValue or maxstring also, but couldn't succeed right now.

Not sure, but hope this clarifies something.

Regards,

Stefan

edit: well, an alternative could maybe look like this:

=concat(aggr(if(rank(total sum(sales),1,1)=1,[store name]),[store nr],org, wk),',')

Return text from aggr() function

Mon, 31 Oct 2011 00:41:54 GMT

Thanks for the explanation. It's clearer now.

Regards,

Marko