Display a zero value

Patricia_Zavisky

How do I display a zero value for a location which has no data. I have 7 locations with only 6 displaying because the 7th location has no data. How do I get the location to display in the table an show a 0% value?

MarkSnodgrass

You would need to create the zero entry in an ETL. You could do this with a join tile so that every location has a row for each type of value you want to report on.

pauljames

@Patricia_Zavisky

In the etl perhaps you could use an add formula file and name the column name then in the formula write this….

IFNULL(column name, 0)

MarkSnodgrass

You would need to create the zero entry in an ETL. You could do this with a join tile so that every location has a row for each type of value you want to report on.

pauljames

@Patricia_Zavisky

In the etl perhaps you could use an add formula file and name the column name then in the formula write this….

IFNULL(column name, 0)

Patricia_Zavisky

Hi Mark,

I have successfully created the column and the column with a zero value is appearing.

I had an issue. The percentage count was not a distinct count e.g. I then created a a beast mode calculation - COUNT(DISTINCT(CASE WHEN Hierarchy of Control_Elimination count = 1 then Parent Record: Uniq IDEND))

This gave the percentage. However, when I untick the 'Show Totals' to stop showing the Grand Total via properties, Total Row my calculation changes. Do you know why?

Patricia_Zavisky

Hi Mark, worked a solution. Instead of showing the value as a % of Total column in the formating of the calculation I edited the beast mode calculation -

COUNT(DISTINCT(CASE WHEN Hierarchy of Control_Elimination count = 1 then Parent Record: Uniq IDEND)) / COUNT(DISTINCT Parent Record: Uniq ID) * 100

Thank you all for your assistance.