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

    Kyle Aspri

      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 NameMonthly Invoice AmountInvoice Start MonthInvoice End Month
      Company A$200January 2015July 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 NameMonthMonthly Invoice Amount
      Company AJanuary 2015$200
      Company AFebruary 2015$200
      Company AMarch 2015$200
      Company AApril 2015$200
      Company AMay 2015$200
      Company AJune 2015$200
      Company AJuly 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.

       

      Thanks in advance,

       

      Kyle