Pivot Displaying 0 as Null

[chriys1337]

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.

[chriys1337]

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

[chriys1337]

Thanks Antonio, that's another possible solution.

[Anil_Babu_Samineni]

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.

[jonathandienst · Accepted solution]

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.

[chriys1337]

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