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

    Top N by profit for each date

    laura.vanzutphen

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

      example.png

       

      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
          Michel Caissie

          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
            laura.vanzutphen

            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

              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.