Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kicchu465
Creator
Creator

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.

Capture.JPG

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.

Capture1.JPG

Note : I have unchecked the Include Null values in dimension Tab and Data Handling tab.

Thanks and Regards,

Kishore

9 Replies
pradosh_thakur
Master II
Master II

if(isnull(Only({$<Year={'$(=vlatestmnth)'},SC=,UT=,AC={'fieldfield'}>}SC &' ' &if(Unit='%','%')))=-1,' ',

Only({$<Year={'$(=vlatestmnth)'},SC=,UT=,AC={'fieldfield'}>}SC &' ' &if(Unit='%','%')))

Learning never stops.
kicchu465
Creator
Creator
Author

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

pradosh_thakur
Master II
Master II

you have two expression ? what are those ? can u post the app with less data or dummy data?

Learning never stops.
Anonymous
Not applicable

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.

OmarBenSalem

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

kicchu465
Creator
Creator
Author

Hi Omar,

Its not working.

pradosh_thakur
Master II
Master II

can you post sample for us to look into ?

Learning never stops.
kicchu465
Creator
Creator
Author

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

Anonymous
Not applicable

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.