Return text from aggr() function

[#1]

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!

[its_anandrjs]

Hi,

Your formula should be like

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

which returns 190

Rgds

Anand

[swuehl · Accepted solution]

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

[#4]

Hi Anand,

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

[#5]

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]

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

[#7]

Thanks for the explanation. It's clearer now.

Regards,

Marko