Replace Zeros with Empty Values

[anuradhaa]

I have an expression and sometime the output value is "0". I want to replace it with ""

expression - sum( {$<Type={AAA}>} Hours )

Thanks

[sunny_talwar · Accepted solution]

You can use an if statement

If(Sum({<Type = {AAA}>} Hours) > 0, Sum({<Type = {AAA}>} Hours))

[sunny_talwar]

or you can do something like this:

Sum({<Type = {AAA}>} Hours) * (Avg({<Type = {AAA}>} Hours)/Avg({<Type = {AAA}>} Hours))

[sushil353]

Hi,

You can also try option to suppress 0 values from expression. setting>presentation>suppress zero value option checked in..

other wise sunny answer should work.

[anuradhaa]

I have three columns in my pivot table, when i use this some records were exclude even the >0 values in another coulm.

so If(Sum({<Type = {AAA}>} Hours) > 0, Sum({<Type = {AAA}>} Hours),'')

Solve the issue

[sunny_talwar]

Your three columns are three expressions or are you just using a pivoted dimension with just 1 expression?

[Anil_Babu_Samineni]

Check from Presentation Tab

[anuradhaa]

Yes, You are correct. In one column i'm getting sums of other two columns and that is the column that i have question.

sum(hours) - [col 1]  - [col 2].

So my solution also wrong even though it display the raw the values are incorrect. Then i have replace coulm names in the expression with whole expression, then issue resolved.

Thanks Sunny.