3 Replies Latest reply on Dec 16, 2016 9:27 AM by Hannah Forristall

    Creating a PY QTD Measure

    Hannah Forristall

      Hi,

       

      I'm trying to create a prior year QTD measure and I'm having trouble getting it to work. This is the formula I am using for the prior year YTD measure:

       

      sum(if YEAR([Date])= YEAR([Reference Date])-1 and

      [Date]<= DATEADD('year', -1,[Reference Date])then [Lbs] end)

       

      Can anyone advise me on how to change the formula so that it will work for QTD?

       

      Thanks so much,

      Hannah

        • 1. Re: Creating a PY QTD Measure
          Tom W

          Hi Hannah,

          Please attach a Tableau Packaged Workbook including your sample data.

           

          It's pretty tough to tell you why it isn't working without seeing the data.

          • 2. Re: Creating a PY QTD Measure
            Simon Runc

            hi Hannah,

             

            So very similar to the way you've created your YtD, we can add some extra checks using the DATEPART function...

             

            In the attached, I've created a parameter (your reference date) which is what controls where we are in QtD...

             

            So first I created a measure for the current QtD

            [Sales - Current QtD]

            IF YEAR([Order Date])= YEAR([Reference Date]) AND

            DATEPART('quarter', [Order Date]) = DATEPART('quarter', [Reference Date])

            THEN [Sales]

            END

             

            and then I created the following for QtD PY (btw I've used Week level, but you could use DATEPART with 'DayofYear', or any other date part depending on how you define the QtD)

            [Sales - PY QtD]

            IF YEAR([Order Date])= YEAR([Reference Date])-1 AND

            DATEPART('quarter', [Order Date]) = DATEPART('quarter', [Reference Date]) AND

            DATEPART('week', [Order Date]) <=DATEPART('week', [Reference Date])

            THEN [Sales]

            END

             

            In the attached, the "how it works" tab lets you see how they are calculating.

             

            You might also find this post handy...Current vs Previous Period to Date Comparison

             

            Hope that helps, but let me know if it's not doing what you need.

            • 3. Re: Creating a PY QTD Measure
              Hannah Forristall

              This worked great Simon!

               

              Thank you both for your help!

               

              Hannah