Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
chriys1337
Creator II
Creator II

Pivot Displaying 0 as Null

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.

1 Solution

Accepted Solutions
jonathandienst

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

14 Replies
sunny_talwar

May be this

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

chriys1337
Creator II
Creator II
Author

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

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

Anil_Babu_Samineni

Why not to use this?

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
antoniotiman
Master III
Master III

Hi Christian,

try this

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

Regards,

Antonio

chriys1337
Creator II
Creator II
Author

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
Creator II
Creator II
Author

Hi Antonia,

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

Anil_Babu_Samineni

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

Set Nullinterpret = '0';

NullasValue rev;

Set NullValue = '-';

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
antoniotiman
Master III
Master III

What about

Text Color :

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