To eliminate Null Values in Pivot Table

[kicchu465]

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

[pradosh_thakur]

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

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

[kicchu465]

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]

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

[Anonymous]

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]

Hi Omar,

Its not working.

[pradosh_thakur]

can you post sample for us to look into ?

[kicchu465]

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]

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.