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

# Running Total Days

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

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:

• ###### 2. Re: Running Total Days

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

• ###### 3. Re: Running Total Days

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

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

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'
)

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

An alternative I've used is:

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

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.

Thanks

• ###### 8. Re: Running Total Days

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

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'

)

Receivables / Sales *

IF YEAR([DateField]) < YEAR(TODAY())
THEN DATEDIFF('day'
, DATEADD('year',DATEDIFF('year',#1/1/1900#,[DateField]),#1/1/1900#) // Beginning of 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

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

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'

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

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

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.

What I'am doing wrong?

• ###### 13. Re: Running Total Days

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'

and DSO by Month

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

ACDM=

DATEDIFF

('day'

)

ACDY and ACDM are a Measure in my calculations

Mark, once again my best regards

Ricardo Mota

• ###### 14. Re: Running Total Days

Hi Alex Kerin

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