12 Replies Latest reply on Jan 15, 2018 5:21 AM by Zhouyi Zhang

    Month with Max sales across region

    Ophelia Daisy

      Seeking help from the experts on this complex crosstab calculation. We are calculating four different measures on a cross-tab.


      Select Month - As per parameter selection

      MAX - Best Sales -  The month with Max sales

      best month Name -  Name of month which had max sales (throughout the entire time period 2000-2017

      RANK - Rank of the selected month (throughout the entire time period 2000-2017. Ranking should be dynamic


      If selected month's result & best month are same and if the Rank is 1, I suppose to do the conditional formatting of Rank & the Best Month column with yellow.



      I manage to bring the sales for the select month as per param selection.


      I am stuck at this stage on how to calculate the Best Sales (MAX of Sales across the month of all years), respective best month and the rank calcs. I tried the rank, however, my rank is applied only to the visible rows on the crosstab, not based on the selected month or based on the entire time period of data. For now, I only have data from 2013-2017 on the attached twbx. However, the original database has data from 1970-2017.


      Please find the attached twbx.



      Thank you so much in advance!. 


      - Ophelia