Replace Missing values with zero and percentage value

[sarfaraz_sheikh]

Dear Folks,

Need one help to solve below problem. You can see three rows below. 1st rows is percentage one and second and third one are values.

What I want is wherever value is zero then in percentage rows it should show 0.00% and in values rows it should show 0.

As per the option in under Presentation Tab we can replace Missing values to whatever we want and that is standard for entire table. But I want to show 0.00% in percentage and 0 in values.

How I can do this. I have tried multiple things but did not work. it is not accepting zero values at all.

Below is my Expression and attached is the QVW. for your reference.

Pick(Wildmatch([Measurement Flag],'SQLU1','SQLU2','EU1'),
Num(Sum([SQL Total Utilization])),
Num(Sum([SQL Total Utilization])),
Num(
Sum([Nominated AllQuarter])/Sum([EncorePool Count]),'0.00%'))

[kaushiknsolanki · Accepted solution]

Hi,

Make a few changes.

1. Change Expression to 

Pick(Wildmatch([Measurement Flag],'SQLU1','SQLU2','EU1'),
Num(Sum([SQL Total Utilization])),
Num(Sum([SQL Total Utilization])),
Num(
If(Isnull(Sum([Nominated AllQuarter])/Sum([EncorePool Count])),(1-1),Sum([Nominated AllQuarter])/Sum([EncorePool Count])),'0.00%'))

2. Go to Presentation Tab and uncheck "Supress Zero Value"

3. Go to Dimension and select "Parameter" and check "Suppress when value is null"

Find the QVW attached.

[sarfaraz_sheikh]

Dear Kaushik,

Thanks for your help.  It works as expected.

However if I change dimension then it did't work. Again blank is coming. Look at the below snapshot cell is highlighted in black square box.

I have changed the Expression in way which you have written something like below to achieve this but did't work.

Pick(Wildmatch([Measurement Flag],'SQLU1','SQLU2','EU1','GP1'),
Num(If(IsNull(Sum([SQL Total Utilization])),(1-1),Sum([SQL Total Utilization]))),
Num(Sum([SQL Total Utilization])),
Num(
If(Isnull(Sum([Nominated AllQuarter])/Sum([EncorePool Count])),(1-1),Sum([Nominated AllQuarter])/Sum([EncorePool Count])),'0.00%'),
If(IsNull(Sum([GPS Score])),'-',Sum([GPS Score]))
)

Updated qvw is attached for your reference.

Thanks

Sarfaraz

[kaushiknsolanki · Accepted solution]

Go to Properties and define "Missing Symbol" as "0" and "Null Symbol" as "0".

[dadumas]

In my opinion, this is the best solution because it retains zeros as numeric when exported to excel.    Just setting the missing symbol to 0 does do that, and just makes it a "text" 0