Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Return text from aggr() function

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!

1 Solution

Accepted Solutions
swuehl
Not applicable

Return text from aggr() function

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

6 Replies
its_anandrjs
Not applicable

Return text from aggr() function

Hi,

Your formula should be like

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

which returns 190

Rgds

Anand

swuehl
Not applicable

Return text from aggr() function

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

Not applicable

Return text from aggr() function

Hi Anand,

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

Not applicable

Return text from aggr() function

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!

swuehl
Not applicable

Re: Return text from aggr() function

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),',')

Not applicable

Return text from aggr() function

Thanks for the explanation. It's clearer now.

Regards,

Marko