14 Replies Latest reply on Dec 11, 2012 4:31 AM by Ricardo Mota

    Running Total Days

    Ricardo Mota

      Hello

       

      I want to calculate the Day Sales Outstanding by month. In that calculation I need to have the total days until the month I want to known.

       

      Attched it's a excel with the example.

       

      With some help I have this formula but ii takes some time to do the query: (The Extract have 2 million rows)

       

      datediff('day', DATE(STR(YEAR([DataLancamento]))+'-01-01'), IF MONTH([DataLancamento]) = 12 THEN DATE(STR(YEAR([DataLancamento])+1)+'-01-01') ELSE DATE(STR(YEAR([DataLancamento]))+'-'+STR(MONTH([DataLancamento])+1)+'-01') END)

       

      This works for relative dates,but if someone have a better idea I will be apreciated.

       

      Thank you very much

       

      Ricardo Mota

        • 1. Re: Running Total Days
          Mark Holtz

          Hi Ricardo,

          I'm unsure what question you are asking. In your Excel sample, it appears you already have the RunningDay value. Are you saying that you don't have a solution for non-relative dates? Are you able to post a packaged Tableau workbook with the data you do have?

          Perhaps some of these formulas may be helpful to you:

          Beginning of This Month: DATEADD('month',DATEDIFF('month',#1/1/1900#,today()),#1/1/1900#)

          Beginning of Next Month: DATEADD('month',DATEDIFF('month',#1/1/1900#,today())+1,#1/1/1900#)

          Beginning of This Year :DATEADD('year',DATEDIFF('year',#1/1/1900#,today()),#1/1/1900#)

          Beginning of Next Year: DATEADD('year',DATEDIFF('year',#1/1/1900#,today())+1,#1/1/1900#)

          End of This Month: DATEADD('day',-1,DATEADD('month',DATEDIFF('month',#1/1/1900#,today())+1,#1/1/1900#))

          End of Next Month: DATEADD('day',-1,DATEADD('month',DATEDIFF('month',#1/1/1900#,today())+2,#1/1/1900#))

          • 2. Re: Running Total Days
            Alex Kerin

            Mark - do you know if these formulas will behave given the different epoch dates out there?

            • 3. Re: Running Total Days
              Mark Holtz

              Good point Alex.  I forget not everyone is as ingrained and programmed to the Microsoft standards as I am. But, since Tableau recognizes the date 1/1/1900 (granted, I am sourcing from MS SQL Server data, using the Microsoft epoch standard), my suspicion is that it will work regardless of the epoch of the source data.

               

              Even if it does cause a problem, theoretically, as long as the same date is used in the DATEDIFF and DATEADD components, it shouldn't matter. I guess it may be more universal to use a future date like 1/1/2500" as a reference point, given the potential for different epoch periodization schemes. (I am no epoch expert, but I presume future dates exist in all schemes.)

               

              The design for these formulas is to only be interested in relative difference from the reference date. So whether you start with the month that is 1344 from 1/1/1900 or 504 from 1/1/1970, it should still be December 2012, and the formulas will seek 1 month beyond that or 1 day before 2 months beyond that...

              • 4. Re: Running Total Days
                Ricardo Mota

                Hi Mark

                 

                "it appears you already have the RunningDay value. "

                I want to calculate the running days like in the excel file. 31, 60, 91 ... depending the month. In excel the calculation it's manual.

                 

                Thanks

                RFM

                • 5. Re: Running Total Days
                  Mark Holtz

                  OK.  I thought that might be what you were getting at... You wanted to be able to replicate the Running Day in your sample in Tableau.
                  So then you are after difference in days between the "Beginning of year" and "End of This Month."

                   

                  Using the formulas I shared, that should be possible using, but you need to replace the today() input with the date field you are trying to assess:
                  DATEDIFF
                       ('day'
                       ,DATEADD('year',DATEDIFF('year',#1/1/1900#,[YourDateField]),#1/1/1900#) //Beginning of this year
                       ,DATEADD('day',-1,DATEADD('month',DATEDIFF('month',#1/1/1900#,[YourDateField])+1,#1/1/1900#)) //End of this month
                       )

                  1 of 1 people found this helpful
                  • 6. Re: Running Total Days
                    Alex Kerin

                    An alternative I've used is:

                     

                     

                    datediff('day',datetrunc('year',[Order Date]),dateadd('month',1,datetrunc('month',[Order Date])))

                     

                    Working backwards, truncate to the 1st of the month, add a month, find difference between 1st of the year and this.

                    • 7. Re: Running Total Days
                      Ricardo Mota

                      Hi Mark

                       

                      The Query it's much faster (2 or 3 seconds) (instead of 1 minute with my calculation) but

                       

                      For January it returns 30 days instead of 31

                      For February it's correct

                      For March it's Correct

                      For April also correct.

                       

                      It's something about the January.

                       

                      Thanks

                      • 8. Re: Running Total Days
                        Ricardo Mota

                        Also one question:

                         

                        If I want to calculate instead a monthly DSO a anual DSO for example the last 5 years:

                         

                        2007 2008 2009 2011 2012

                         

                        The formula for the past years it's easy:

                         

                        Receivables / Sales * 365 but for 2007 it's 366 and for 2012 it's not over.

                         

                        What do you think?

                         

                        Thanks

                        • 9. Re: Running Total Days
                          Mark Holtz

                          Ahhh, sorry.  Rather than the "Beginning of This Year" you really would want the "End of Last Year" so that the calculation gives # of days between 12/31/2011 to 1/31/2012 (31) instead of between 1/1/2012 and 1/31/2012 (30).

                           

                          DATEDIFF

                               ('day'

                               ,DATEADD('day',-1, DATEADD('year',DATEDIFF('year',#1/1/1900#,[YourDateField]),#1/1/1900#)) //End of Last year
                               ,DATEADD('day',-1,DATEADD('month',DATEDIFF('month',#1/1/1900#,[YourDateField])+1,#1/1/1900#)) //End of this month
                               )

                           

                          Your Annual DSO could be:

                          Receivables / Sales *

                          IF YEAR([DateField]) < YEAR(TODAY())
                          THEN DATEDIFF('day'
                               , DATEADD('year',DATEDIFF('year',#1/1/1900#,[DateField]),#1/1/1900#) // Beginning of year
                               , DATEADD('day',-1,DATEADD('year',DATEDIFF('year',#1/1/1900#,[DateField])+1,#1/1/1900#)) // End of this year
                               ) + 1
                          ELSE DATEDIFF('day'
                               , DATEADD('year',DATEDIFF('year',#1/1/1900#,today()),#1/1/1900#) // Beginning of year
                               , today())

                          END

                          • 10. Re: Running Total Days
                            Ricardo Mota

                            Hi Mark

                             

                            Thank you very much

                             

                            The formula for Monthly DSO it works perfectly! Thank you again.

                             

                            The anual it gives the total days of the year, but when I have YTD in the filter, the values of receivables and sales are YTD and the formula for the days are all year.:

                             

                            For example: It Returns 365 for 2010/2011 and 344 for 2012. It should return 344 for 2010 and 2011.

                             

                            Thank you so much

                             

                            Ricardo

                            • 11. Re: Running Total Days
                              Mark Holtz

                              Oh. I misunderstood.  You want to compare YTD 2012 to YTD 2011 or YTD 2010,  not 2012 to 2011 to 2010...

                               

                              So that would be similar, and slightly simpler:

                              DATEDIFF('day'
                              , DATEADD('day',-1, DATEADD('year',DATEDIFF('year',#1/1/1900#,[YourDateField]),#1/1/1900#)) //End of Last year
                              , DATEADD('day',DATEDIFF('day',#1/1/1900#,[YourDateField]),#1/1/1900#))

                              Don't forget, 2012 was a leap year...
                              Cheers!

                              1 of 1 people found this helpful
                              • 12. Re: Running Total Days
                                Ricardo Mota

                                Hi Mark

                                 

                                I tried to understand your formula, but it's away out of my league!!

                                 

                                It works like you can see on the screen but in the calculation I used MAX to return me the YTD, but the results aren´t the expected.2012-12-10_18-14-46.png2012-12-11_09-15-16.png

                                 

                                What I'am doing wrong?

                                 

                                Thanks for your patient!

                                • 13. Re: Running Total Days
                                  Ricardo Mota

                                  Hi Mark

                                   

                                  Hi figure it out. It's the running_sum!! Not the formula.

                                   

                                  For those interested in one formula of DSO YTD by Year:

                                   

                                  SUM(Receivables) / SUM(Sales) * MAX(ACDY) where ACDY:

                                   

                                  ACDY =

                                  DATEDIFF('day'

                                  , DATEADD('day',-1, DATEADD('year',DATEDIFF('year',#1/1/1900#,[YourDateField]),#1/1/1900#)) //End of Last year
                                  , DATEADD('day',DATEDIFF('day',#1/1/1900#,[YourDateField]),#1/1/1900#))

                                   

                                  and DSO by Month

                                   

                                  Running_Sum(SUM(Receivables)) / Running_Sum(SUM(Sales)) * MAX(ACDM) where ACDM:

                                   

                                   

                                  ACDM=

                                  DATEDIFF

                                       ('day'

                                       ,DATEADD('year',DATEDIFF('year',#1/1/1900#,[YourDateField]),#1/1/1900#) //Beginning of this year
                                       ,DATEADD('day',-1,DATEADD('month',DATEDIFF('month',#1/1/1900#,[YourDateField])+1,#1/1/1900#)) //End of this month
                                       )

                                   

                                  ACDY and ACDM are a Measure in my calculations

                                   

                                  Mark, once again my best regards

                                   

                                  Ricardo Mota

                                  • 14. Re: Running Total Days
                                    Ricardo Mota

                                    Hi Alex Kerin

                                     

                                    Thank you for your answer.

                                     

                                    It works flawlessly. With this one it works for Year or Month.

                                     

                                    I've tried to understand all of this formulas but it's like in excel when we have a IF then Another IF and Another, and we arrive at a point that's hard to know where we are!!!!

                                     

                                    Thank you once again and Best Regards

                                     

                                    Ricardo