Solved: Re: Pivot Displaying 0 as Null - Qlik Community

chriys1337 · ‎2017-06-20

Hello,

I am having a big pivot table, where a lot of 0 (Zero) Values are occurring.

The users would instead like to see - (Null Values).

So I have re-worked my expression to an if statement::

if(
sum({<Year_Week={'$(vL.ActualCalenderWeekminus4)'}>}rev)
=0, null(),
sum({<Year_Week={'$(vL.ActualCalenderWeekminus4)'}>}rev)
)

Unfortunately the performance is suffering, therefore I would like to ask you If there is a smarter way to achieve the result the users would like to see?

Thanks for any help.

jonathandienst · ‎2017-06-21

I don't think you need a conditional. Have you tried '0;(0);-' (or '0;0;-')as a format string. For example

Put this in a text box:

=Num(0, '0;(0);-')

You should see a '-' in the box. You can also enter that string into the format box for the column in Properties | Number for the pivot table.

The three parts of the format separated by semicolons are +value, -value, 0 value.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

sunny_talwar · ‎2017-06-20

May be this

Alt(Sum({<Year_Week={'$(vL.ActualCalenderWeekminus4)'}>}rev), 0)

chriys1337 · ‎2017-06-20

Hi Sunny,

I have just tested it, unfortunately it's not what I am looking for.

In my table there are 0's and I want them do display as '-' in the table.

The alt function would display the null values as 0... its the other way around.

Thank you anyway!

sunny_talwar · ‎2017-06-20

I guess if statement might be your only option here in that case

Anil_Babu_Samineni · ‎2017-06-20

Why not to use this?

if(sum({<Year_Week={'$(vL.ActualCalenderWeekminus4)'}>}rev)=0, '-',sum({<Year_Week={'$(vL.ActualCalenderWeekminus4)'}>}rev))

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

antoniotiman · ‎2017-06-20

Hi Christian,

try this

sum({<Year_Week={'$(vL.ActualCalenderWeekminus4)'},rev-={0}>} rev)

Regards,

Antonio

chriys1337 · ‎2017-06-20

Thanks for your opinion Sunny, so I am assured that I have the best solution possible.

@Anil.

I just had the feeling that the if-statement is performance-intensive for my pivot table (its a really big one) and the users don't like buggy scrolling , therefore I wanted to check if something more performance-friendly than the if-statement is available.

chriys1337 · ‎2017-06-20

Hi Antonia,

same result, the 0s are displayed as 0 and not as desired as line. But thanks.

Anil_Babu_Samineni · ‎2017-06-20

Would you like to go in script? Please add this and check how this works?

Set Nullinterpret = '0';

NullasValue rev;

Set NullValue = '-';

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

antoniotiman · ‎2017-06-20

What about

Text Color :

If(Column(N)=0,ARGB(0,0,0,0)) //Where N = nr Column of Expression