8 Replies Latest reply on Jan 8, 2013 2:41 PM by Marc Moussa

    Current YTD vs Prior YTD

    Stan Bush

      Hi All,

       

      I've searched this forum and the internet. Still can't find exactly what I'm looking for. Any help would be greatly appreciated.

       

      My requirement is simple: I need to display current year to date and prior year to date in the same worksheet or at the least in a separate sheet on the same dashboard.

       

      The calculation would look like this:

       


      Oct-10Nov-10Dec-10Jan-11Feb-11Mar-11Apr-11May-11Jun-11Jul-11Aug-11Sep-11Oct-11Nov-11Dec-11Prior YTDCurrent YTD
      Joe Smith75171511919161114818820132941

       

      Prior YTD is calculated by adding Oct-10, Nov-10, and Dec-10 : 7+5+17 = 29

      Current YTD is calculated by adding Oct-11, Nov-11, and Dec-11: 8+20+13=41

       

      Attached is a packaged Tableau workbook.

       

      Screenshot of the Tableau data I need these calculations on:

       

      CurrentPriorYTD.png

        • 1. Re: Current YTD vs Prior YTD

          Hi Stan,

           

          I have an SQL calculation I perform on the table I upload onto Tableau,

          It works for me.

           

          Let me know if u need any assistance on this!!!

           

          Where is what I do:

           

          /* We add a column called id_date and id_ytd in the table we want to use, in my case,

          I have a sales table, in which I have the period_code, and I want to add these columns to that table.

          In this example I'll call it dbo.xx

          */

          Alter table dbo.xxx add id_date int

          GO

           

           

          Alter table dbo.xxx add id_ytd varchar(25)

          GO

           

           

          --we create a table called dbo.aux_period

          CREATE TABLE dbo.Aux_Period

          (ID int,Period_Code datetime)

          GO

           

           

          /* In this procedure, we fill in the table we just created (dbo.aux_period),

          each time we want to execute this procedure we have to drop this table and create it again*/

           

           

          --We only create this procedure once!

           

           

          CREATE PROCEDURE Insert_Aux_Period

          as

          Declare @i as int

          Declare @Period_Code as datetime

          Declare @YTD1 as varchar(25)

          Declare @YTD2 as varchar (25)

           

           

          Set @i=0

           

           

          insert into dbo.Aux_Period (Period_Code)

          (select distinct period_code from dbo.xxx)

           

           

          Declare CURSOR_FECHA CURSOR

                    for select period_code from dbo.Aux_Period order by period_code desc

           

           

          Open CURSOR_FECHA

          Fetch next from CURSOR_FECHA into @Period_Code

          while @i<24

                    BEGIN

                              while @@fetch_status=0

                              begin

                                        update dbo.Aux_Period set id=@i where period_code=@Period_Code

                                        set @i=@i+1

                                        update dbo.xxx  set id_date=@i where period_code=@Period_Code

           

                                        Fetch next from CURSOR_FECHA into @Period_Code

                              end

                    END

                    close CURSOR_FECHA

                    deallocate CURSOR_FECHA

           

           

          /* My data has only 24 months, thats why I only use 2 YTD */

           

           

          SET @YTD1=(select 'YTD '+CAST(year(getdate()) as varchar(4)))

          SET @YTD2=(select 'YTD '+CAST(year(getdate())-1 as varchar(4)))

           

           

          /*In this step,we indicate to what YTD each date belongs to, in my case I always do this for the data of the previous month, that's why I have the sentence month(period_code)<=Month(Getdate())-1, if it was the month we are in, we just write month(period_code)<=Month(Getdate())

          */

           

           

          update dbo.xxx set id_ytd=@YTD1 where ( month(period_code)<=Month(Getdate())-1 and year(period_code)=Year(Getdate()) )

          update dbo.xxx set id_ytd=@YTD2 where ( month(period_code)<=Month(Getdate())-1 and year(period_code)=year(Getdate())-1 )

           

           

          GO

          SET QUOTED_IDENTIFIER OFF

          GO

          SET ANSI_NULLS ON

          GO

           

           

           

          -- We then execute this procedure

          exec Insert_Aux_Period

          • 2. Re: Current YTD vs Prior YTD
            Milind Jog

            Is this what you are looking for? If yes I can send you the calculations that I do in Tableau workbook.

            Stan Bush.png

            • 3. Re: Current YTD vs Prior YTD
              Andrew Watson

              What if you create a calc with the formula: if month([Discharge Date]) >= 10 then [Cases] end

               

              Remove the month pill and put the new calc in the text shelf? You can set the year by adding an AND statement about the year into the calc if you need to.

               

              Andrew

              • 4. Re: Current YTD vs Prior YTD
                Michael Sonu

                YTD.png

                 

                Are you lokking the result like above. then just add filter on the month and select the months. This is how i calculate my YTD in a simple way.

                • 5. Re: Current YTD vs Prior YTD
                  Jonathan Drummey

                  Hi Stan,

                   

                  Here's an alternative using a calculated field based on the current date, based on this post http://community.tableau.com/thread/119588.

                   

                  Cheers,

                   

                  Jonathan

                  • 6. Re: Current YTD vs Prior YTD

                    I couldn't figure out where you were getting these numbers for Joe Smith

                     

                    The calculation would look like this:

                     


                    Oct-10Nov-10Dec-10Jan-11Feb-11Mar-11Apr-11May-11Jun-11Jul-11Aug-11Sep-11Oct-11Nov-11Dec-11Prior YTDCurrent YTD
                    Joe Smith75171511919161114818820132941

                     

                    Prior YTD is calculated by adding Oct-10, Nov-10, and Dec-10 : 7+5+17 = 29

                    Current YTD is calculated by adding Oct-11, Nov-11, and Dec-11: 8+20+13=41

                     

                    They didn't match the data provided.

                     

                    Attached is my solution on Sheet 2. Sheet 1 breaks out what I did in Sheet 2. Let me know if this works out.

                    • 7. Re: Current YTD vs Prior YTD
                      Kevin MacDonell

                      Here's what I do. It involves creating three simple calculated fields. These could perhaps be rolled into one, but for ease of explanation, I'll go with three. I report on fundraising results for a university, therefore my reports (and these examples) are based on Gift Date. I need the ability to compare this year's fundraising totals (so far) against the previous four or five years, year-to-date. Our fiscal year starts April 1, which as you know creates complications when you attempt simple comparisons of dates. Tableau thinks April is month number 4 no matter what, even if you've told it what month comes first.

                       

                      So here we go. First I create a measure called FiscMonth_GiftDate, which returns a "corrected" month number for every gift date. April will be "1" and March will be "12":

                       

                      if datepart('month',[Gift Date])<4 then datepart('month',[Gift Date])+9

                      else datepart('month',[Gift Date])-3

                      end

                       

                      Then I create a measure I call FiscMonth_Today, which does the same thing for today's date:

                       

                      if datepart('month',today())<4 then datepart('month',today())+9

                      else datepart('month',today())-3

                      end

                       

                      And then, to tie it all together, a Dimension which evaluates to either True or False. I call it simply "YTD":

                       

                      [FiscMonth_GiftDate] < [FiscMonth_Today] OR ([FiscMonth_GiftDate] = [FiscMonth_Today] AND day([Gift Date]) <= day(today()))

                       

                      Finally, I drag YTD to Filters, and select True. Done.

                      • 8. Re: Current YTD vs Prior YTD
                        Marc Moussa

                        Hello,

                         

                        Check out the solution explained at the following address:

                        http://www.interworks.com/blogs/ktreadwell/2012/12/28/date-calculations-tableau