1 Reply Latest reply on Jan 28, 2019 9:53 AM by Esther Aller

    Dynamic trailing 4 quarters growth calculation with fiscal quarters (=strings)

    Renata Feher

      Hi Everybody,

       

      I have a dataset where I have

      - different countries

      - different fiscal quarters

      - related booking numbers

      - related KPI number

       

      I would like to plot a scatter diagram where the user can choose which quarter/quarters they want to compare with their respective previous year's quarters.

      the graph would look something like this, explaining booking growth and the avg KPI for the selected quarters. (I have used excel calculation to get the growth numbers)

      with excel calc.png

       

      my problem is calculating the trailing 4 quarter growth number. I want the user to be able to select the quarters they want to compare - not restricting them to only 1 quarter. But if I use the 'Fiscal Quarter' as a filter, I cannot calculate the previous year's same period booking data. The KPI should show the selected quarter(s) average value, so that is not a problem)

      I tried a few LOD solutions, but neither of them worked for me.

      The calculation I am after is the following:

      let's say, the user chose FY19 Q1- FY19 Q2. That means, for the growth, I need to get the sum of the booking number for this period and divide it with the sum of the bookings between FY18 Q1 - FY18 Q4. That last bit is missing for me.

       

      I hope someone can point me in the right direction. I have attached a sample dataset for reference.

       

      Thank you in advance,

      Renata

        • 1. Re: Dynamic trailing 4 quarters growth calculation with fiscal quarters (=strings)
          Esther Aller

          Hi Renata,

           

          I think your best bet will be to have two parameters that define a date range, which can be used to calculated before selected bookings and booking for the same date range a year back.

           

          See Creating a Filter for Start and End Dates Using Parameters for an example. Keep in mind you do not want to filter the entire view to the selected date range. Instead you want to use the condition to create an IF statement that will return bookings. I have attached a quick example workbook as well.

           

          There is another possible solution that is much more complicated. If you did a self-join to duplicate every quarter for every quarter, then you could use a Set Action to allow the user to ctrl+click to select multiple quarters from a source worksheet that lists all quarters. The major downside of this method is that it would massively duplicate your data, which would cause performance issues if there the data set is large to begin with.

           

          Hope this helps