4 Replies Latest reply: Jan 22, 2015 7:43 AM by Ernesto García

# Using aggr with top ranking of two dimensions

I am currently using the calculated dimension:

=aggr(if(rank(sum([Sales]))<=5,[Customer]),[Customer])

Then if I suppress null on this dimension and add the expression sum(Sales), I get the sales of the top 5 customers.  I realise I can do this by just restricting the dimension and using a field but I am working towards a more complicated aggr which I can't get working.

The data has entries containing a customer, what brand they bought and how much they paid....millions of entries.  I want to find the above aggr over the top 5 brands. I'm at a loss, I don't know if I should use a calculated dimension and aggr expression or a multiple if statement aggr expression or restrict with a set analysis.  All The discussion I've read stops at ranking top something over one dimension.

I want how much sales of top 5 (by sales) brands to top 5 (by sales) customers, can someone help me modify the above dimension or create a new expression?

I tried using the dimension:

=aggr(if(rank(sum([Sales]))<=5,[Customer], rank(sum([Sales]))<=5,[Brand]),[Customer], {Brand])

for example to create a table of top 5 customers buying top 5 brands.

This does not work when I use the expression sum(sales)..

• ###### Re: Using aggr with top ranking of two dimensions

I think you can do it easier than that. Do you have any restriction to use more than one dimension?? Also you can use the limit dimensions to show the top 5.

• ###### Re: Using aggr with top ranking of two dimensions

Hi there.

Take a look at the document attached.

Ernesto.

• ###### Re: Using aggr with top ranking of two dimensions

Ernesto, that is great I hadn't thought of doing separate dimensions! One thing that I'd like to fix, however, is that I really don't need the separate customers and sales being evident (I used a bar chart and there is a bar for each customer and a colour for each brand stacked in the bar).  What I really want is just a total value with one bar...is this possible using the dimension route?

• ###### Re: Using aggr with top ranking of two dimensions

Hi there.

Sorry for the delayed response, I've been a little busy lately.

The trick is to "hide" the "top 5  brands" dimensions by disabling the leyend and setting a single color for all the chart. See the example.

Kind regards,

Ernesto.