Forum Discussion
How to include rows with a 'zero count' in a grouped table
Hi All
I have a table which hold resource utilisation for employees on a per year/month basis:
EMP1, 2017, 1, 75%
EMP2, 2017, 1, 83%
EMP3, 2017, 1, 44%
EMP1, 2017, 2, 66%
EMP2, 2017, 2, 93%
EMP3, 2017, 2, 28%
I have created a computed (conditional) column to categorise what range the utilisation falls into so I can plot the results.
i.e. RANGE=
IF UTILISATION < 0 THEN "RANGE0"
ELSEIF UTILISATION <= 10 THEN "RANGE1"
ELSEIF UTILISATION <=20 THEN "RANGE2"
...etc
ELSEIF UTILISATION <=100 THEN "RANGE10"
I'm then using a table grouping (COUNT on column RANGE) to try and find out how many values are included in a particular range.
It works fine, however when I chart the results I only see data where there is a positive count > 0 - which I suppose is strictly how a group function would work.
But I want to see the full range of results whether there is a count or not.
Is there a way of returning the zero count when grouping or is there a better way f achieving this?
Thanks
Hmmm interesting, I think one of the solutions is in adapting your data model.
If you create a new table that contains only a column with the Range descriptions like "Range0" , "Range1" etc, then create a connection between your data table and that table, matching on that range description. Finally, instead of using the data table's "range" dimension, you use the one from your new table. In that case, when you check "show items with no data" like below, it will show the empty rows as well.
Not sure if this is the cleverest solution, but it will work.
3 Replies
Hmmm interesting, I think one of the solutions is in adapting your data model.
If you create a new table that contains only a column with the Range descriptions like "Range0" , "Range1" etc, then create a connection between your data table and that table, matching on that range description. Finally, instead of using the data table's "range" dimension, you use the one from your new table. In that case, when you check "show items with no data" like below, it will show the empty rows as well.
Not sure if this is the cleverest solution, but it will work.
Simple solution but it works - thank you!!!
Something to try, perhaps try to convert your numeric value to text using a CONCATENATE function with a "" or " ".
