Pivot Table (If)

[pauledrich]

I have a target table with 3 simple expressions with dates as a dimension, for this year there are no targets for Jan & Feb so therefore the Calculation [Results]/[Target] as % is off the scale and I want to display 0 if there is no target or a null.

I have tried to only show a Zero if the Target is 0 which isn't working.

=if(Target>0,Sum([Results]/[Target]),0)

Thanks in advance

P

[chiru_thota]

have you tried below ?

=if(sum(Target)>0,Sum([Results]/[Target]),0)

[pauledrich]

Thanks for the reply Chiranjeevi.

I had tried that one also but had a syntax error with bad field names in the second Sum - having checked the target field is null so that's probably the issue?

[chiru_thota]

Are you able to resolve the issue ?...If not can you upload some sample data so that we can have a look ?

[#5]

can you try this:

=if(sum(Target)<=0 or isnull(Target), 0, Sum(Results)/sum(Target))

and also deselect "suppress Zero-Values" on your chart properties

Regards

[pauledrich]

I found the answer which was not related to a null - creating a normal expression with a set modifier of >0 for the field gives me the desired results.

Thanks for the help.