5 Replies Latest reply on Sep 12, 2018 2:44 AM by Lucie Brett

    Rank and select top N after table calculation

    Lucie Brett

      Hello everyone,

       

      I have mocked up what I am trying to achieve in the attached workbook. A client has asked to show the top 3 sub-categories with the greatest absolute (-ive or +ive) YoY sales growth.

       

      I am using a table calc to get the YoY growth (have hidden previous year column), I have then added this calc as a discrete pill to the left of the sub-category wrapped in abs() and with "1-" at the beginning so that it orders from highest absolute % difference to lowest.

       

      Now I need to select the top 3 however I can't seem to get this to work using rank() or index(), due to the hidden column and the table calc I imagine. Does anyone have any ideas how to do this or indeed a better way of calculating?

       

      This needs to be fully dynamic so there will be additional filters which might need to be added to context.

       

      Thanks in advance for your input!