5 Replies Latest reply on Feb 12, 2016 4:41 AM by Łukasz Majewski

How to Plot Value Over Time When Data Set Has Only 'Start' and 'End' Dates

Hey Everyone,

I have a seemingly simple problem that has stumped me and many Tableau "Doctors", and I'm hoping you can help me out.  I have a data set with a 'start month', an 'end month', and a 'monthly value', and I'm looking for a way to plot monthly value over time.  Here is a simplified version of the data set I'm using:

 Customer Name Monthly Invoice Amount Invoice Start Month Invoice End Month Company A \$200 January 2015 July 2015

I've tried all sorts of calculations and techniques to allow me to plot 'total invoice amount' by month, but the only way I have been able to accomplish this successfully is by cross joining my data set with a table containing all months present in my data set, so that I have a row for each company and month.  So, for "Company A" in my data above, the new data set resulting from the cross join would look like this (7 rows instead of 1).

 Customer Name Month Monthly Invoice Amount Company A January 2015 \$200 Company A February 2015 \$200 Company A March 2015 \$200 Company A April 2015 \$200 Company A May 2015 \$200 Company A June 2015 \$200 Company A July 2015 \$200

While the cross joining method does work, it does not scale well, especially when you have thousands of customers spanning many years.  Plus, there are times when we want to look at daily, or weekly amounts, which would make the resulting cross-joined data set even larger.

Again, I'm looking for a calculation or technique that will allow me to plot value over time (in this case invoice amount) when I have "Start" and "End" dates in my data set, without having to cross join to another table.  I have attached a packaged workbook with sample data.  We're still using version 8.3, but if there is a solution in Tableau 9, that would be great too.

Kyle

• 1. Re: How to Plot Value Over Time When Data Set Has Only 'Start' and 'End' Dates

Kyle

I think you are taking right approach and as long as I know, it is the best way.

Tableau cannot "Create" data, then it requires master data of Month (//or other time grain) x Customer Name.

Thanks,

Shin

• 2. Re: How to Plot Value Over Time When Data Set Has Only 'Start' and 'End' Dates

You could use UNION instead of a CROSS JOIN as shown by  Joe Mako  in  Re: Headcount, when given the Arrival & Departure Dates.

The benefit of a UNION is that it "only" doubles the number of rows.

The cost is described in #3 and #4 in  Re: Headcount, when given the Arrival & Departure Dates.

You might like to up-vote  Date period as multiple marks  and thus encourage Tableau to invent a better solution for date duration vs calendar periods.

• 4. Re: How to Plot Value Over Time When Data Set Has Only 'Start' and 'End' Dates

I believe this scenario is why Multi-Values Parameters has not been implemented.

Theoretically, one could instantiate a Multi-Valued Parameter via a Calculated Field. And then use another Calculated field to only represent dates between Start and End Dates.

This would essentially create a Cross-Joined query (Which could blow things up depending on the number of rows, and cardinalality).

Until this is figured out, we have what we have.

Ideally, if you can Cross Join the data on the backend, do it. It makes working with Tableau a lot easier.

If you can't, then the methods Kettan pointed to should help you out.

I'll also add this in, as it is very relative to the situation, and provides an alternative method.

TDT: Data Scaffolding on Vimeo

Regards,

Rody

• 5. Re: How to Plot Value Over Time When Data Set Has Only 'Start' and 'End' Dates

I think I cracked this one although the solution is rather cumbersome.

See attached 9.2 workbook.

The trick I used is to split the available records into 2 sets: one to calculate the overall minimum date and one for overall max. This is done by introducing another date field called [Unified Date]:

```IF [Invoice End Month] <> [LoD max date] THEN [LoD min date]
ELSE [LoD max date] END
```

Here is why I said it was cumbersome: the above formula might not yield the expected results, i.e. overall min&max. It may be the case with very scarce dataset where one record holds both min&max. Anyways it works with your 5 rows...

Having at least 2 records ranging in dates we can fill in the gaps using "Show Missing Values" option. Here on the level of Months:

Then I introduced the following calcs:

[min date]

```RUNNING_MIN(min([Invoice Start Month]))
```

[The Date]

```DATEADD('month', index()-1, [min date])
```

[the sum]

```if (LOOKUP(MIN([Invoice Start Month]),-index()+1)<=[The Date]
OR LOOKUP(MIN([Invoice Start Month]),-index()+size())<=[The Date] )
AND (LOOKUP(MAX([Invoice End Month]), -index()+1)>=[The Date]
OR LOOKUP(MAX([Invoice End Month]), size()-INDEX())>=[The Date])
THEN WINDOW_MAX(MIN([Monthly Invoice Amount]))
else 0 end
```

Even though we are showing missing months any standard dimension or measure is NULL in these "missing" months in between since every record belongs to either our min or max unified date. But with a help of table calculations we can lookup the right thing.

This particular set of pills did the trick: