Aggr() and if()

[Anonymous]

Hi Everyone,

I am trying to build a bar chart with defined dimensions. The dimensions are defined by =valuelist('Top','Rest') , and the measures should be presented as the sum of Invoice Amounts of top 5 performers and sum of Invoice Amounts of the rest.

I tried to define this as the script shown below. For the 'Top' part I am getting a correct value, but the second part is showing empty.

if( valuelist('Top','Rest')='Top',

sum(aggr(  IF( aggr ( Rank(sum([Invoice Amount])),[Customer Name])<=5,

sum([Invoice Amount])),[Customer Name])),

sum(aggr(  IF( aggr ( Rank(sum([Invoice Amount])),[Customer Name])>5,

sum([Invoice Amount] )),[Customer Name]))

)

Could anyone help me out with how to follow from here on?

Thanks!

Edil

[sunny_talwar · Accepted solution]

Look here:

Q-Tip # 14 – AGGR() and Synthetic Dimensions | Natural Synergies

[#3]

Hi Try This,

if( valuelist('Top','Rest')='Top',
IF( aggr ( Rank(sum([Invoice Amount])),[Customer Name])<=5,sum([Invoice Amount])),[Customer Name])),

IF( aggr ( Rank(sum([Invoice Amount])),[Customer Name])>5, sum([Invoice Amount] )),[Customer Name])))

pls correct the syntax if i am wrong   else could u pls share your application

[Anonymous]

Thanks Sunny T,

So instead of using a Synthetic Dimension (i.e =valuelist('Top','Rest')), which turned out to be tricky to work together with when using Aggr() function, I chose a simpler way. Which is:

Dimension:

if( aggr ( Rank(sum([Invoice Amount])),[Customer Name])<=5,  'Top', 'Rest');

Measure:

sum([Invoice Amount])

This easily does the thing I need.

I think I just overthought the difficulty of the project.