1 Reply Latest reply on Nov 14, 2018 12:16 PM by Michael Gillespie

    YoY or PoP Without Dates

    Tyson Andrews
    Tyson Andrews Nov 14, 2018 12:06 PM

      Hello,

      I am working with a Fiscal Year and Period (1-13)

      I have a parameter built with a FY+Per dimension. I need to be able to select a FY+Per then have the sales value, previous period sales value, and previous years sales value of that FY+Per show up in KPI boxes.

       

      Unfortunately I can't make them a date. So my Question is using those two dimensions, how can I do a year over year or period over period calculation without a dateadd function?

       

       

      Thanks,

      -Tyson

        • 1. Re: YoY or PoP Without Dates
          Michael Gillespie
          Michael Gillespie Moderator Nov 14, 2018 12:16 PM (in response to Tyson Andrews)

          The answer depends on how much control you have over changes to the source data (or rather, ADDITIONS to the source data).

           

          The standard way of dealing with this is the "date scaffold" method.  This involves creating a separate (usually Excel) calendar where you map a specific date to its corresponding Fiscal Year, Month or Period, Week and Day.  In fact, you create all the possible columns you might want to have available to you in your vizzes, so in your case, you might want another column with Fiscal Period-Fiscal Year as a combined field (201801).

           

          You should have an explicit column that you will use to join this fiscal calendar data source to your main business data.  In many cases, I create 2 columns of "Date" in the 2018-01-01 format: the first column is my DATEJOIN column, and my second column is my DATE column.  This new data source has to be the LEFT side of a join, but otherwise it's pretty straightforward.

           

          I do this so often with so many of my clients that I have a template file in this format ready to go.  Then it's just a case of mapping specific dates to your custom fiscal periods.

           

          In your workbook, you use the real dates to do date math, but use the Fiscal period columns to DISPLAY the results of your date math.  Search around the forums for "scaffold" and you'll see lots of examples.

           

          If you cannot do this, then you'll have to do the date math by hand.  It's possible, but it's difficult and very dependent on how you treat mismatched fiscal periods (e.g., what happens with Feb 29?  Do you ever hit a case where you have 54 weeks in one fiscal year? stuff like that...).