3 Replies Latest reply on Apr 18, 2018 8:24 AM by Michel Caissie

# Top N by profit for each date

Hi all,

I would like to create a graph, that shows the average profit of the top 3 highest-profit subcategories. It feels like it should be an easy task, but I just can't make it work. See example workbook attached.

What I am trying to do:

Create a line graph with date on the x-axis and average profit on the y-axis. The visualized values should be the average profit of the top 3 highest-profit sub-categories.

So for january 2014 that would be: (326+287+251)/3=288

So for february 2014 that would be: (1173+252+221)/3=548,67

So for march 2014 that would be: (1410+313+292)/3=671,67

etc...

I already tried index and rank, but I can't make it work.

Is there someone that can help me with this?

Many thanks!

• ###### 1. Re: Top N by profit for each date

Laura,

you can have a specific computation for each TopX  using  LOD.

First compute the  SUM_Profit(Month_SubCat) with

{FIXED DATETRUNC('month', [Order Date]),[Sub-Category]:SUM([Profit])}

Then compute a boolean returning true if the SUM_Profit(Month_SubCat)  is the top of the month

[SUM_Profit(Month_SubCat)] = {FIXED DATETRUNC('month', [Order Date]): MAX([SUM_Profit(Month_SubCat)])}

For IsTop2  use

[SUM_Profit(Month_SubCat)] = {FIXED DATETRUNC('month', [Order Date]): MAX(if not [IsTop1] then [SUM_Profit(Month_SubCat)] end)}

And for IsTop3 use

[SUM_Profit(Month_SubCat)] = {FIXED DATETRUNC('month', [Order Date]): MAX(if not [IsTop1] and not [IsTop2] then [SUM_Profit(Month_SubCat)] end)}

And for the AVG Top3

AVG({FIXED DATETRUNC('month', [Order Date]): AVG(if [IsTop1] or [IsTop2] or [IsTop3] then [SUM_Profit(Month_SubCat)] end)})

In your post you have a miscalculation for February, you should get  748 and not 548

and for March Top2 is 362 and not 313 so the avg is 695

Michel

• ###### 2. Re: Top N by profit for each date

Thanks for your reply Michel. I guess it works, but it feels so cumbersome. Because if I would like to use the top 8, I have to make so many different Calculated Fields..

If anyone has a less complex way to do this, would love to hear about it. Meanwhile I'll implement Michel's solution.

• ###### 3. Re: Top N by profit for each date

You can try this;

WINDOW_SUM( if RANK( SUM( [Profit] ) ) <= [Parameters].[TopX] then  SUM( [Profit] )  end ) / [Parameters].[TopX]

compute on Month - SubCat , restarting every month.

For this you need to bring the Sub Category on the Detail shelf.

And to get a single line you need to add a filter like this

LOOKUP(MIN([Sub-Category]),0)

The important here, is to filter on a sub Category having data in every months. In your real data, if you don't have a sub Category having data in every months, this solution will not work. For example, if you switch the filter selection from Art to Accessories , the October 2014 dot disapear and the line changes. To resolve this , you would need some data densification at the source, to make sure that every Months have at least one row for each Sub Category with at least a null value for the profit.