The thing is if you use a case when statement like you presumable do in your dimension, then each case is only counted in one ‘When’ statement. The only way to make the same case get counted in multiple rows is to use RUNNING_TOTAL. The next issue is that RUNNING_TOTAL has to be applied to all rows; it is not possible to write a case when statement that using RUNNING_TOTAL on some rows and just gives the count in others but the good news is I’ve found you a workaround.
So, my idea is to use two KPIs. In one of them Running total is used for <30 Days, <90 Days and <180 Days and for all other categories it just returns a 0. In the other KPI the Case Count (or whatever your counting in your table) is given for the other categories and 0 for the ones on which you want to use the running total. Then in the plot you stack the two KPIs and give them the same colour to give the illusion of them being the one column (which would have the desired height).
For technical reasons, to use this solution, you would need to change the order of your columns so that the running total columns are at the end. So “6. >180 Days” would have to be “3. >180 Days”. Sorry about that
Now for a technical explanation of how to implement it.
Make a Column Chart and add:
- The dimension is the same as in your original table (which I assume is a CASE WHEN statement).
- Make a KPI with the code:
PU_COUNT_DISTINCT( DOMAIN_TABLE (), “ACTIVITIY TABLE “.”_CASE_KEY”, NOT(“TABLE”.“DAY VARIABLE” < 180))
This will give you case count for every category other than <30 Days, <90 Days and <180 Days. If you aren’t using case count, then instead of “ACTIVITIY TABLE “.”_CASE_KEY” concatenate all the important columns of the table your counting so that you have a unique identifier for what your counting. Concatenating columns uses the following syntax: “table”.”column1” || “table”.”column2” || “table”.”column3”.
Make another KPI with the code:
RUNNING_TOTAL(PU_COUNT_DISTINCT( DOMAIN_TABLE (), “ACTIVITIY TABLE “.”_CASE_KEY”, “TABLE”.“DAY VARIABLE” < 180))
The running total will be formed over the case count. Due to the filter on “TABLE”.“DAY VARIABLE” < 180 the rows other than <30 Days, <90 Days and <180 Days will have a count of 0. However, if “>180 Days” comes after “<180” it would have the same value as “<180”, which is why the category needs to be moved.
Choose the option “Stacked (on Primary Axis)” and give it the same colour as the previous KPI.
Add the KPI: COUNT_TABLE(“CASE TABLE”) (or what every our counting)
This KPI is needed because otherwise for complicated technical reasons the plot won’t have one bar per Category.
You don’t actually want to see this KPI so it can be hidden by setting “Series Color” to transparent. If you also chose “Stacked (on Primary Axis)” then the columns are no further apart than before. It would however increase the range of the axis, if that’s a problem you can manually change the Axis Range by selecting “Manual-Range” in “Primary Value Axis” options.
Hide all evidence of this sneaky work around by hiding the legend (option is found in 'Diagram Area & Legend Options). Just remember to make it clear in the title of the component what’s being counted.
I hope this helps. If you have any further issues, then please write back.