9 Replies Latest reply on Aug 20, 2015 8:59 AM by Ankit Sobti

    YTD calculation for 2 years Data

    Ankit Sobti


      Hi friends,

       

      I am using Tableau 8.1 version .

       

      I am creating a view that has Values against dates which is of 2014 and 2015 (two year data).

       

       

      i want to create YTD (Year to date) for values in such a way that when date from 2014 year is selected then it must calculate YTD value from starting date = 1/7/2014 to the date selected and if date is selected for year 2015 then it must calculate YTD from 1 jan 2015(for example) to the date selected.

       

      i am unable to share sample file.

       

      Thanks in advance

      Ankit

       

        • 1. Re: YTD calculation for 2 years Data
          Ankit Sobti

          Hi Team pl find the attachment and copy pasting query :

           

          I am using Tableau 8.1 version .however this sample file is in version 9

           

          I am creating a view that has Values against dates which is of 2014 and 2015 (two year data).

           

          i want to create YTD (Year to date) for values in such a way that when date from 2014 year is selected then it must calculate YTD value from starting date = 1/7/2014 to the date selected and if date is selected for year 2015 then it must calculate YTD from 1 jul 2015(for example) to the date selected.

           

           

          so if i have selected 2-9-2014 then it must give me YTD sum from 1-jul-2014 to 2-9-2014 and if the date selected is 18-8-2015 then it must give me YTD from 1 July 2015 to 18-8-2015

           

           

          Please note i am taking July as starting date because it is starting of fiscal year as per business requirement.


          Thanks in advance

          1 of 1 people found this helpful
          • 2. Re: YTD calculation for 2 years Data
            Joe Oppelt

            Attached is one way to do this.

             

            I am displaying the FYTD value in the sheet's title for now.

             

            I created a parameter for the user to specify a date.  You can capture the user's date selection in many ways, but this is how I did it.

             

            I created a start-of-range calc to figure out what year we are looking at and then plugged in "7/1/" in front of that.  So we'll pull from 7/1/(year selected) through the date selected.

             

            There are other ways to do that too.  It's just what I chose.  (I wanted to compartmentalize the steps.)

             

            Then I did a window sum of all the values within the range.  Again, there are multiple ways to do this.

            • 3. Re: YTD calculation for 2 years Data
              Joe Oppelt

              Just want to point out that I approached this in a MM/DD/YYYY format, but you can set up formats any wan you need.

              1 of 1 people found this helpful
              • 4. Re: YTD calculation for 2 years Data
                Ankit Sobti

                Hi Joe, thanks for the reply however I can see that when I select date the value is not adding up to that date from the start date. it is showing only current value only. is there sth I am missing .

                • 5. Re: YTD calculation for 2 years Data
                  Joe Oppelt

                  My calc value is only showing up in the sheet1 title, not on the actual viz.

                  1 of 1 people found this helpful
                  • 6. Re: YTD calculation for 2 years Data
                    Ankit Sobti

                    Hi Joe, Thanks for the quick help,

                     

                    its working great for YTD for 2014 however I just checked calculations ,

                     

                    1) looks like when I select date after 1 jul 2015 it is giving me sum of values from 1 jul 2014... for any date after 1 july 2015 it must take start date as 1 july 2015 only.. can you pl look it into.

                     

                    2) Also can you please help me to select User-selected date in format similar to Dates that I have mentioned.

                     

                    Appreciate your help Joe if you can show me above 2 concerns.

                     

                    Thanks Joe

                    • 7. Re: YTD calculation for 2 years Data
                      Joe Oppelt

                      If I pick July 16, 2014, I get a value of 63.  And I see the three relevant values in the data add up to 63.

                       

                      If I pick June 30, 2015 I get 1897.  I assume that's correct for all values from July 1, 2014 through June 30, 2015.  I didn't add them up.

                       

                      If I pick July 16, 2015, I get a value of 76.93.  That's 38.42 + 38.51, which are the two data values from July 1, 2015 through July 16, 2015.

                       

                      I did have to change the logic of [Range Start Date].  I didn't take into account that the fiscal year will span a calendar year, so if the user selects June 1, 2015, it was not starting in July 1, 2014.

                       

                      The new calc for start date looks like this:

                       

                      if Month([User-selected date])>=1 and MONTH([User-selected date])<7 then

                      // if here then user selected a date from Jan-1 through June-30. 

                      // Start date of this fiscal year is Jul 1 of the previous year.

                      date("7/1/"+STR(YEAR([User-selected date])-1))

                      ELSE

                      date("7/1/"+STR(YEAR([User-selected date])))

                      end

                       

                      But I was getting 76.93 for July 16, 2015 even before that change.

                      1 of 1 people found this helpful
                      • 8. Re: YTD calculation for 2 years Data
                        Ankit Sobti

                        Hi Joe,

                         

                        thanks for the help, I will check if it works and let you know...:)

                        • 9. Re: YTD calculation for 2 years Data
                          Ankit Sobti

                          Hi Joe, How are you...thank you soooo much for the quick help its working fine for me as well....I applied the first logic and the soln is awesm

                           

                          appreciate your help Joe