1 2 Previous Next 15 Replies Latest reply on Dec 13, 2012 12:49 PM by Yonel Letellier

# number of days in month of future years

Please I need to get the number of days for the months  of future years, ie, jan 2013 - Dec 2015.

I have this formula to get the number days in the months in my database: DATEADD('month',1,DATETRUNC('Month,[Date])) - DATEADD('month',DATETRUNC('Month',[Date]). My data base have data from Jan 10 to Sep 12.

I need to get the number of days for the months Oct12 - Dec 15.

Thanks a lot

• ###### 1. Re: number of days in month of future years

What is the base date for those future dates - i.e. how are you going to tell Tableau that you want the number of days in Oct 2014?

• ###### 2. Re: number of days in month of future years

I created a parameter "Forecast to" with range 12/10/2012 to 12/31/2015, with DATE as Data Type and Automatic format, step size 1 month.

I use this parameter for forecasting futures months variables, but one of this variable consists in Daily Average Balances, that is why I need the number of days of the future months.

Thanks again for any help.

Yonel

• ###### 3. Re: number of days in month of future years

IF MONTH([Future Date]) = 2 THEN 28

ELSEIF MONTH([Future Date]) = 4 OR

MONTH([Future Date]) = 6 OR

MONTH([Future Date]) = 9 OR

MONTH([Future Date]) = 11 THEN 30

ELSE 31

END

--Shawn

• ###### 4. Re: number of days in month of future years

Thanks for your reply but it did not work. It did not give any values for Oct12 - Dec 15.

The closed I got but give me only Sep 2012 as values for Oct 12-Dec15, is:

IF ISNULL(LOOKUP(ATTR(DATEADD('month', 1, DATETRUNC('month',[Date])) - DATETRUNC('month',[Date])),0))

THEN

PREVIOUS_VALUE(DATEADD('month', 1, DATETRUNC('month',[Forecast to])) - DATETRUNC('month',[Forecast to]))

ELSE

END

Thanks

• ###### 5. Re: number of days in month of future years

Yeah, I should have said this before: You need to post a sample packaged workbook for us to help you with your problem. Solutions depend on so many different variables it is difficult to help you play a mental game of chess without pieces or a board. [Especially when we find out later you're actually playing Go. ]

--Shawn

• ###### 6. Re: number of days in month of future years

@Yonel - What Shawn said. Packaged workbooks are *extremely* necessary to help with issues regarding table calcs.

@Shawn - I strongly recommend against building your own date calcs, and using the built-in calcs of the system wherever possible. There's just too much chance of error...for example, the calc you wrote does not take into account leap years, and in three months the end of that Dec 2015 forecase will be into a leap year. Here's the simplified version of what Yonel had written that will work (Yonel had put a version of this into the second post).

Jonathan

• ###### 7. Re: number of days in month of future years

Jonathan agreed. (See I can agree with you ) I actually looked up the leap years specifically to see if I needed to account for it within his specified date range, and found I didn't have to, so I didn't. [Besides by then he'll be in V 8 or 9 and everything will be different.]

--Shawn

• ###### 8. Re: number of days in month of future years

Thanks guys for your help. Attached is a packaged workbook. Hope it helps.

I need the formula "Days in Month Frcst" to show the actuals number of days from Oct 2012 to Dec 2015.

Yonel

• ###### 9. Re: number of days in month of future years

Hi Jonathan, any ideas of how I can do this.

Thanks a lot for any help

• ###### 10. Re: number of days in month of future years

Hi Yonel,

Thanks for posting a packaged workbook, your issue makes a lot more sense now. You are using Tableau's date padding (Show Missing Values), and when you do that Tableau is generating dates but we don't actually have direct access to those padded values. However, we do have access to the "ordinal" table calculations like INDEX() and SIZE(). I'll have to work out how to do that.

Jonathan

• ###### 11. Re: number of days in month of future years

Thanks

• ###### 12. Re: number of days in month of future years

Here you go. The basic process is as follows:

1. Identify the first month in the data. I filtered out the values that had a Null for Period Date to make this easier.

2. Identify the current month based on the INDEX() calc. Note that this could return incorrect values if there were missing months, however the MONTH(Date) field is padded with Show Missing Values so there are no missing months. Nice, that.

3. Calculate the number of days in the month.

This uses three nested table calculations:

Calc First Month - gets the first month of the displayed data. It's optimized so that the the calc is only performed once.

Calc Date - identifies the month

Calc Days in Month - uses a variation of the formula above to get the date.

Each calculation in the nest needs to have its Compute Using set to Advanced... on Year of Period Date and Month of Date, like so:

In the linechart view, instead of the MONTH(Date) being used on Columns, the Calc Date is used:

Hope this helps,

Jonathan

• ###### 13. Re: number of days in month of future years

Thanks Jonathan,

Could you please post the workbook that you worked on. It will be easier for me to foloow your instructions.

Thanks again,

Yonel

• ###### 14. Re: number of days in month of future years

Sorry about that, it's attached now.

1 2 Previous Next