Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
May be this
Alt(Sum({<Year_Week={'$(vL.ActualCalenderWeekminus4)'}>}rev), 0)
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!
I guess if statement might be your only option here in that case
Why not to use this?
if(sum({<Year_Week={'$(vL.ActualCalenderWeekminus4)'}>}rev)=0, '-',sum({<Year_Week={'$(vL.ActualCalenderWeekminus4)'}>}rev))
Hi Christian,
try this
sum({<Year_Week={'$(vL.ActualCalenderWeekminus4)'},rev-={0}>} rev)
Regards,
Antonio
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.
Hi Antonia,
same result, the 0s are displayed as 0 and not as desired as line. But thanks.
Would you like to go in script? Please add this and check how this works?
Set Nullinterpret = '0';
NullasValue rev;
Set NullValue = '-';
What about
Text Color :
If(Column(N)=0,ARGB(0,0,0,0)) //Where N = nr Column of Expression