5 Replies Latest reply on Feb 13, 2014 3:16 PM by Jonathan Drummey

# Generating more than one date with DATEADD function

Hi All -

I am trying to create a viz that will forecast out dates relative to how frequently a field says they run (i.e. daily, weekly, monthly, etc.). In the attached packaged workbook, you till see that using the DATEADD function at the day level and basing the number of days to be added off an if statement (If 'Daily' then 1 elseif 'Weekly' then 7 end) I am able to forecast one date forward from the START_DATE.

What I would like is for that calculation to continue until it is greater than or equal to a specified END_DATE. So if a campaign ran on 2/10/2014 and was slated to run daily till the end of the month (2/28/2014) then I would like the forecast calculation to generate 18 discrete values (2/11/2014-2/28/2014). Similarly at the weekly frequency it would forecast 2 values (2/17/2014 and 2/24/2014). Any insights or direction to helpful links would be greatly appreciated.

-Matt

• ###### 1. Re: Generating more than one date with DATEADD function

Jonathan Drummey I know you have played around with dates a lot in the past, have you ever come across a scenario like this that you can give me the go/no-go on feasability?

Much appreciated.

-Matt

• ###### 2. Re: Re: Generating more than one date with DATEADD function

Matthew,

I don't know if this will help you exactly, it is a bit of a hack, but you may be able to find a way to get it to work for you.

Under some circumstances, tableau is willing to perform a calculation in a cell during a table calculation EVEN IF THERE WAS NO DATA THERE!

So what I have done is choose an extra dimension from your dataset to set across columns to create a number of cells that might be N forecast periods out from where you currently are.

Then you can use the INDEX() , (or FIRST() or LAST() functions probably) to see which forecast interval you are on.

Then you can use more table calcs to look up your project's start and end dates, plus recurrence interval in order to perform your calculation.

Here is a workbook that contains several intermediate calculations to demonstrate the effect.

You can do it all with just a single calculated field if you are interested.

• ###### 3. Re: Generating more than one date with DATEADD function

That's great stuff David! I had a feeling I needed to involve an index, just not quite sure where.

That is exactly what I'm looking for from the side of the calculation, but now I need to figure out what I can set along the columns shelf so that these dates will extend further than the 3 values that are currently there.

I really appreciate the insight.

Thanks.

Matt

• ###### 4. Re: Re: Generating more than one date with DATEADD function

Matthew,

I agree you'll need to find a solution to figure out how many records you'll get as far as the prediction.

You could do a number of things, including for example adding a set of rows at the bottom of your data set.

I have done this before, you could say, add a new column called "Recurrance Interval", and populate a few rows containing numbers going from say 1-20 or 1-31 (daily for 1 month)  and null in all other columns.

OR if you use your real dataset, you might have more different values in the column than exist in this sample, and it coudl resolve your entire problem.

• ###### 5. Re: Generating more than one date with DATEADD function

Hi Matthew,

Posting from my phone here while wiring for the bus to get through the storm. David's solution (I haven't looked at it, just read his post) looks like one solution.

You could also check out http://drawingwithnumbers.artisart.org/tableau-data-blending-sparse-data-multiple-levels-of-granularity-and-improvements-in-version-8/ for an example of using domain padding to pad out data, using a single extra row instead of columns. Demo #2 of http://drawingwithnumbers.artisart.org/creating-a-dynamic-parameter-with-a-tableau-data-blend/ also does this. In addition, Joe Mako did a demo of using domain padding explicitly for forecasting in a Think Data Thursday awhile back, I don't have the link handy.

Jonathan

1 of 1 people found this helpful