Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Discussion Board for collaboration related to QlikView App Development.

Announcements

CUSTOMERS ONLY: Now accepting **customer** applications for the 2023 Luminary Program: **SUBMIT NOW**

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Re: Replace Missing values with zero and percentag...

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

sarfaraz_sheikh

Contributor III

2020-05-28
04:46 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

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

964 Views

2 Solutions

Accepted Solutions

kaushiknsolanki

MVP

2020-05-28
05:22 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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".

Kaushik

If reply is satisfactory, please "Like" the post.

If reply is solution, please mark as "Correct".

kaushiknsolanki

MVP

2020-05-29
09:42 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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".

Kaushik

If reply is satisfactory, please "Like" the post.

If reply is solution, please mark as "Correct".

912 Views

4 Replies

kaushiknsolanki

MVP

2020-05-28
05:22 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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".

Kaushik

If reply is satisfactory, please "Like" the post.

If reply is solution, please mark as "Correct".

sarfaraz_sheikh

Contributor III

2020-05-28
01:25 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

926 Views

kaushiknsolanki

MVP

2020-05-29
09:42 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Kaushik

If reply is satisfactory, please "Like" the post.

If reply is solution, please mark as "Correct".

913 Views

dadumas

Creator II

2020-10-21
11:16 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

828 Views