topic Re: Pivot Displaying 0 as Null in QlikView

Pivot Displaying 0 as Null

chriys1337 — Tue, 20 Jun 2017 15:41:58 GMT

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.

Re: Pivot Displaying 0 as Null

sunny_talwar — Tue, 20 Jun 2017 15:44:35 GMT

May be this

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

Re: Pivot Displaying 0 as Null

chriys1337 — Tue, 20 Jun 2017 15:54:33 GMT

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!

Re: Pivot Displaying 0 as Null

sunny_talwar — Tue, 20 Jun 2017 15:58:08 GMT

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

Re: Pivot Displaying 0 as Null

Anil_Babu_Samineni — Tue, 20 Jun 2017 15:59:37 GMT

Why not to use this?

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

Re: Pivot Displaying 0 as Null

antoniotiman — Tue, 20 Jun 2017 16:04:15 GMT

Hi Christian,

try this

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

Regards,

Antonio

Re: Pivot Displaying 0 as Null

chriys1337 — Tue, 20 Jun 2017 16:06:04 GMT

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.

Re: Pivot Displaying 0 as Null

chriys1337 — Tue, 20 Jun 2017 16:14:01 GMT

Hi Antonia,

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

Re: Pivot Displaying 0 as Null

Anil_Babu_Samineni — Tue, 20 Jun 2017 16:16:56 GMT

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

Set Nullinterpret = '0';

NullasValue rev;

Set NullValue = '-';

Re: Pivot Displaying 0 as Null

antoniotiman — Tue, 20 Jun 2017 16:38:54 GMT

What about

Text Color :

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

Re: Pivot Displaying 0 as Null

chriys1337 — Wed, 21 Jun 2017 06:29:50 GMT

Hi Anil,

I have put your code into my Frontend QVW ( I am using the QDF layer structure), but nothing changed.

What is it expected to do and do I have to locate the code maybe in my Datamodel somewhere?

Kind Regards,

Chris

Re: Pivot Displaying 0 as Null

chriys1337 — Wed, 21 Jun 2017 06:30:19 GMT

Thanks Antonio, that's another possible solution.

Re: Pivot Displaying 0 as Null

Anil_Babu_Samineni — Wed, 21 Jun 2017 06:56:12 GMT

Yes, We have to declare this in Data model itself.

What my intention with the code we want to show - where we get 0 that will deserve.

Re: Pivot Displaying 0 as Null

jonathandienst — Wed, 21 Jun 2017 07:05:34 GMT

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.

Re: Pivot Displaying 0 as Null

chriys1337 — Wed, 21 Jun 2017 10:42:44 GMT

Thanks Jonathan, most of the times the answer is so easy, it works! 🙂