2 Replies Latest reply on Dec 9, 2017 2:50 PM by Zhouyi Zhang

    YTD, PY, PYTD & Variance with parameter

    Samantha Bailey

      Hello Tableau Experts,

       

      I am so much in love with Tableau these days, I have been working on Excel and its super exciting to see how things get simpler by using Tableau.

       

      I am trying to understand and practice the following calculations. I suppose to use these calculations very often but I am still not feeling confident about writing them on my own. I don't see much help regarding this on any of the official documentation provided by Tableau. My Fiscal year should be set for May.

       

      Following are the calculations I am looking forward to learning.

       

      Fiscal Year: May

      The return type of the calculated field should be a Date. I don't want to return the calc field as the measure, as I can re-use them for many of my reports.     

       

      YTD (Current Year To Date) = From May 1 until today

      PY (Prior Year) = From May 1 of the prior year till 30 of April (of this year) as per Fiscal Year.

      Compare YTD with PY = Current year - Prior Year

      Compare YTD with PYTD = Compare the measure values between YTD date with PTYD (i.e., if YTD is (may 1 till 9 dec of 2017, then the comparison with PY should be may 1 till 9 dec of 2016)

      With Parameter: How do I change the year based on the parameter selection. (Parameter: Select FY). Although my parameter shows the years in the dropdown list, however, the year doesn't reflect the years as FY i.e ( 2017 as FY 2018 in my parameter)

       

      I have tried creating the calc for the YTD and PY. However, I am not sure if this is the current calculation. I am attaching the twbx along with the YTD, PY calcs.

       

      Please let me know if I am making sense.

       

      Also, are there any local tableau community around NY? how do I find them?

       

       

      Thanks

      Samantha

        • 1. Re: YTD, PY, PYTD & Variance with parameter
          Norbert Maijoor

          Hi Samantha,

           

          Find my approach as reference below and stored in attached workbook version 10.3 located in the original thread.

           

           

          1. D1. FY date: dateadd('month',12,[Order Date])

           

          2. D2. FY date (Months):

           

          3. M1. YTD (Current Year to Date):

          if datediff('year',[D1. FY date],today())=0

          and [D1. FY date]<=today()

          and [D2. FY date (Months)]>=5 then [Sales] END

           

          4. M2. PY (Prior Year):

          if (datediff('year',[D1. FY date],today())=1 and  [D2. FY date (Months)]>=5)

          or (datediff('year',[D1. FY date],today())=0 and  [D2. FY date (Months)]<=4)

          then [Sales] END

           

          5. M3. PYTD (Prior Year to Date)

          if (datediff('year',[D1. FY date],today())=1

          and [D2. FY date (Months)]>=5)

          and [D1. FY date]<=dateadd('year',-1,today())

          then [Sales] END

           

          Regards,

          Norbert

          • 2. Re: YTD, PY, PYTD & Variance with parameter
            Zhouyi Zhang

            Hi, Samantha

             

            Not quite sure what you want to try to achieve, and not clear about the usage of parameter.

            anyway, please see below calculation about how to get fiscal year, YTD, PY calcualtion.

             

             

            Hope this could help

             

            ZZ

            1 of 1 people found this helpful