
1. Re: Top N by profit for each date
Michel Caissie Apr 17, 2018 10:33 AM (in response to laura.vanzutphen)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]),[SubCategory]: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
laura.vanzutphen Apr 18, 2018 1:58 AM (in response to Michel Caissie)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
Michel Caissie Apr 18, 2018 8:24 AM (in response to laura.vanzutphen)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([SubCategory]),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.