topic Re: Replace Missing values with zero and percentage value in QlikView

Replace Missing values with zero and percentage value

sarfaraz_sheikh — Thu, 28 May 2020 08:46:35 GMT

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

Re: Replace Missing values with zero and percentage value

kaushiknsolanki — Thu, 28 May 2020 09:22:31 GMT

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.

Re: Replace Missing values with zero and percentage value

sarfaraz_sheikh — Thu, 28 May 2020 17:25:13 GMT

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

Re: Replace Missing values with zero and percentage value

kaushiknsolanki — Fri, 29 May 2020 13:42:04 GMT

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

Re: Replace Missing values with zero and percentage value

dadumas — Wed, 21 Oct 2020 15:16:13 GMT

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