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"
IF RUNNING_SUM(SUM([Number of Records])) > 93 THEN
"Group 1 - Incremental"
ELSE "Group 1 - Base"
ELSEIF ATTR([Group]) = "Group 2"
IF RUNNING_SUM(SUM([Number of Records])) > 60 THEN
"Group 2 - Incremental"
ELSE "Group 2 - Base"
Any help is greatly appreciated.
If you can share the packaged workbook, it will help the people in the community.
Until then, you may learn something thru this post.
(little bit long, sorry)