Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
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

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.

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post

kaushiknsolanki

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

 

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post

4 Replies
kaushiknsolanki

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.

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
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

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

 

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
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