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

    Using aggr with top ranking of two dimensions

      I am currently using the calculated dimension:




      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)..