Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
sarfaraz_sheikh
Contributor III
Contributor III

Replace Missing values with zero and percentage value

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.

M2.png

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%'))

 

2 Solutions

Accepted Solutions
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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.

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

 

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

4 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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.

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
sarfaraz_sheikh
Contributor III
Contributor III
Author

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.

D2.png

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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

 

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
dadumas
Creator II
Creator II

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