6 Replies Latest reply: Oct 30, 2011 8:41 PM by Marko Banjanin

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!

• Return text from aggr() function

Hi,

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

which returns 190

Rgds

Anand

• Return text from aggr() function

Hi Anand,

• 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

• 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!

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

• Return text from aggr() function

Thanks for the explanation. It's clearer now.

Regards,

Marko