

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
To eliminate Null Values in Pivot Table
Hi All,
I have a requirement to eliminate null values in Pivot table in Qliksense.
I have used the below expression as measure.
Only({$<Year={'$(=vlatestmnth)'},SC=,UT=,AC={'fieldfield'}>}SC &' ' &if(Unit='%','%'))
The Output as below.
There are some null value displayed as '-'. Could please help how Can I remove these Null values and change the background colour to grey for these values.
My Output Should like as below.
Note : I have unchecked the Include Null values in dimension Tab and Data Handling tab.
Thanks and Regards,
Kishore


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
if(isnull(Only({$<Year={'$(=vlatestmnth)'},SC=,UT=,AC={'fieldfield'}>}SC &' ' &if(Unit='%','%')))=-1,' ',
Only({$<Year={'$(=vlatestmnth)'},SC=,UT=,AC={'fieldfield'}>}SC &' ' &if(Unit='%','%')))


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Pradosh,
Thanks for your reply.
I am sorry the above expression is not working and still I could see the null Values '-''.
Thanks,
SK


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
you have two expression ? what are those ? can u post the app with less data or dummy data?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
In the expression properties maybe set the text and background colour expressions to display grey if the expression evaluates to null(). The dash for null will still be there, but not be visible and both the text and the background will be the same grey.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
a dump idea ! let''s see if it works;
change ur expresseion as follow:
if(Only({$<Year={'$(=vlatestmnth)'},SC=,UT=,AC={'fieldfield'}>}SC &' ' &if(Unit='%','%'))&'hello'='hello','',Only({$<Year={'$(=vlatestmnth)'},SC=,UT=,AC={'fieldfield'}>}SC &' ' &if(Unit='%','%')))


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Omar,
Its not working.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
can you post sample for us to look into ?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I have used the below Background and Text Expressions but still I Could see the Null Values '-'.
Background Expression:
=if(Only({$<Year={'$(=vlatestmnth)'},SC=,UT=,RAG={'Green'},Code={'Year'}>}Year),$(vGreen),
if(Only({$<Year={'$(=vlatestmnth)'},SC=,UT=,RAG={'Amber'},Code={'Year'}>}Year),$(vAmber),
if(Only({$<Year={'$(=vlatestmnth)'},SC=,UT=,RAG={'Red'},Code={'Year'}>}Year),$(vRed),RGB(128,128,128))))
Text Expression:
=if(Only({$<Year={'$(=vlatestmnth)'},SC=,UT=,RAG={'Green'},Code={'Year'}>}Year),White(),
if(Only({$<Year={'$(=vlatestmnth)'},SC=,UT=,RAG={'Amber'},Code={'Year'}>}Year),White(),
if(Only({$<Year={'$(=vlatestmnth)'},SC=,UT=,RAG={'Red'},Code={'Year'}>}Year),White(),RGB(255,255,255))))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Maybe try like this :
Background Expression:
=if(Only({$<Year={'$(=vlatestmnth)'},SC=,UT=,RAG={'Green'},Code={'Year'}>}Year),$(vGreen),
if(Only({$<Year={'$(=vlatestmnth)'},SC=,UT=,RAG={'Amber'},Code={'Year'}>}Year),$(vAmber),
if(Only({$<Year={'$(=vlatestmnth)'},SC=,UT=,RAG={'Red'},Code={'Year'}>}Year),$(vRed),RGB(128,128,128))))
Text Expression:
=if(Only({$<Year={'$(=vlatestmnth)'},SC=,UT=,RAG={'Green'},Code={'Year'}>}Year),White(),
if(Only({$<Year={'$(=vlatestmnth)'},SC=,UT=,RAG={'Amber'},Code={'Year'}>}Year),White(),
if(Only({$<Year={'$(=vlatestmnth)'},SC=,UT=,RAG={'Red'},Code={'Year'}>}Year),White(),RGB(128,128,128))))
If still no joy, then you probably need to post a sample qvf.
