4 Replies Latest reply on Aug 16, 2017 9:38 AM by hassan.dar

    Running Total of a Field per month between defined Start & End Dates

    hassan.dar

      Tableau Desktop Version: 9.2.4 (Cannot upgrade due to IT restrictions)

      Data Type: Excel Workbook

       

      Edit: Attaching Sample Workbook & Excel (Tab "Desired View" in Excel Workbook has an example of what the table SHOULD look like).

       

      Hello,

       

      I have data in the following format:

       

      DepartmentHeadcount
      Start DateEnd Date
      Dept 1501-05-201731-12-2017
      Dept 1304-08-201731-12-2017
      Dept 2304-01-201831-03-2019
      Dept 3201-05-201731-12-2017
      Dept 4101-03-201731-12-2017

       

      I want to create a running total of the "Headcount" field, but only for the dates a given headcount value falls within, right the way through to the MAX() end date in the data set (example below; "..." represents columns omitted for brevity):

       

      DepartmentMar '17
      ...May '17...Aug '17...Dec '17...Jan '18...Mar '19
      Dept 1...5...8...8......
      Dept 2............3...3
      Dept 3...2...2...2......
      Dept 41...1...1...1......

       

      I have managed to get close by following the answer in this post: Running Total with Start and End Dates 

      • I've pivoted "Start Date" and "End Date", and put the resulting "Pivot field values" into Columns (expanded from Year to Month)
      • Dragged "Headcount" to Label/Text to generate a sum of the headcount per month
      • Added Quick Table Calculation > Running Total to the above Sum of Headcount
      • Edited the Table Calculation to include "Month of Pivot Field Values" and "Quarter of Pivot field values" in Addressing for Running Along...

       

      This provides the correct calculation except for one specific day: Dec 31st 2017 where it mysteriously doubles the value from December 29th 2017 (this does not happen on Dec 31st 2018 or any other month that I can see) - this in turn throws off the value for the entire month of December 2017.

       

      Is there a way to fix the issue with 31st December 2017? Or a better way to approach this?

       

      Note:

      - After alot of searching and reading (especially http://redheadedstepdata.io/lookup-vs-transactional/) I am aware my data may need re-shaping either at source or through custom SQL, I'd like to keep that as a last resort if possible (otherwise can go with this solution)

      - I've tried a number of other methods from the listing in FAQ:  Open & Close Dates and various other forum posts (including trying to make my data set work with the method described in: http://kb.tableau.com/articles/knowledgebase/show-records-within-period-of-time) to no avail