1 Reply Latest reply on Mar 16, 2016 12:28 PM by Shinichiro Murakami

    Running sum for base and incremental sums by month

    Tyler McGuffin

      I'm attempting to track the number records for two groups on a monthly basis beginning in April as that is my organization's fiscal year start. Each group has their own base target and all records past this target are considered incremental records. I have written a calc that correctly sorts records into the "incremental" category once the base target is reached.

       

      The issue I'm having is that when the records are put in the table, there is an problem with the month in which the records switch over from "base" to "incremental". For example, for 'Group 1' the base target is 93. However, when this target is met in November, all the records are put into the "incremental" group when 4 should remain in the "base" group and 10 should spill over into "incremental". If the date is expanded to include DAY then this issue is resolved, but I don't want the DAY to appear in the columns shelf, I only want month and year. Tableau doesn't seem to be able to aggregate to the level I want without adding it to the shelf and ruining the view. I've included the calc and some pics to illustrate the issue.

       

      This is my table calc:

       

      IF ATTR([Group]) = "Group 1"

      THEN

          IF RUNNING_SUM(SUM([Number of Records])) > 93 THEN

          "Group 1 - Incremental"

          ELSE "Group 1 - Base"

          END

      ELSEIF ATTR([Group]) = "Group 2"

      THEN

          IF RUNNING_SUM(SUM([Number of Records])) > 60 THEN

          "Group 2 - Incremental"

          ELSE "Group 2 - Base"

          END

      END

       

      RecordsByMonth.png

      RecordsByDay.png

      Any help is greatly appreciated.