2 Replies Latest reply on Dec 7, 2017 8:35 AM by Norbert Maijoor

    YTD or QTD Calculations for Filter

    charley.lam

      Hi All,

       

      I'm working on a chart that has some company data filtered by the Region, Country, Entity as well as the quarter. I'm currently working on parameter/filter that can allow the user to choose whether they want to see the data on a Year to Date (YTD) or Quarter to Date (QTD) basis. It's the first time I'm doing this sort of calculation straight off Tableau so I am struggling. It would be great if some experts here can help me out.

       

      I attached a sample workbook onto this thread for everyone's reference.

        • 1. Re: YTD or QTD Calculations for Filter
          Joe Oppelt

          I started looking at this.

           

          You would serve yourself better if you had a date field in there.  Doing date manipulations (such at YTD) is so much easier with dates.


          Having said that, so far I took your data and created a year field from your [Period] field.  (And I moved both [Period] and [year] to dimensions.)

           

          With a year field, you can do YTD by grabbing all the stuff within a given year.  However, how do you want to determine the data that determines the "to date" step in computing YTD or QTD?  That's going to matter.

          • 2. Re: YTD or QTD Calculations for Filter
            Norbert Maijoor

            Hi Charley,

             

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

             

             

            1. D1. Date: MAKEDATE(int(mid([Period],1,4)),int(mid([Period],5,2)),1)

             

            2. M1. YTD: if DATEDIFF('year',[D1. Date],today())=0 and [D1. Date]<today() then [Value] END

             

            3. M2. QTD: if DATEDIFF('quarter',[D1. Date],today())=0 and [D1. Date]<today() then [Value] END

             

            4. M3 M3. YTD/QTD

            if [YTD/QTD]='YTD' then [M1. YTD]

            elseif [YTD/QTD]='QTD' then [M2. QTD ] END

             

            5. Drag required objects to the indicated locations

             

             

            Regards,

            Norbert