Hide/suppress categories in a chart when measure is zero

[seb24c]

I'm creating a report with several charts using the same query. In each chart, where the measure is zero for a certain category, I don't want the category to appear in the chart. I can't filter out the zeros in the query itself because for each chart there would need to be a different filter. I'd like to keep all the charts using the one query because so many of the fields and filters are common between them.

I'm using legacy charts (for the customization options) and I tried the suppression property, but it doesn't seem to do anything. I've tried all its various options and the categories with a zero measure remain.

I looked for a way to use conditional formatting (intending to try Box Type = None) but this doesn't seem to be a property on the Categories object.

Currently the query has just one calculation which is shared by all charts--employee turnover rate (# of employees who left / # of all employees). The charts then display this rate by different criteria: department, employee type, race/ethnicity, etc. Because of this, the calculation does not have the "for" option for scope, i.e. count ( distinct [PERSON_UID] ) rather than count ( distinct [PERSON_UID] for [DEPARTMENT] ).

I did try making a separate calculation for one of the charts, to see if that is why the suppression property was not working (thinking that a shared calculation would not be zero in the query, only in the chart) but still the suppression did nothing.

So I'm not sure what else to do, or if what I'm attempting is even possible. It does seem like suppression should do something but perhaps I'm just not using it correctly. Any other suggestions?

[adam_mc]

I think I remember another thread on this subject and the solution was to convert the zeros to nulls as null values won't appear on charts.
You may need to create another calculation with a CASE statement converting your value to null if it's zero.
I'm not sure if this is functional in your situation, but it may work for you.

Hope this helps.
Adam.

[seb24c]

Thanks for the suggestion! Unfortunately it didn't work. I think that whether the calculation is zero or null, the category exists in the query so it has a spot in the chart.

[oscarca]

Try to choose the measure that you want to suppress. In the properties pane, open data format and change missing value to number and missing value characters to 0. See if that solves the suppress issue.

[seb24c]

Thanks oscarca. That didn't work either, unfortunately.