topic Re: Pivot Table (If) in QlikView

Pivot Table (If)

pauledrich — Tue, 11 Mar 2014 16:21:33 GMT

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

Re: Pivot Table (If)

chiru_thota — Tue, 11 Mar 2014 16:24:09 GMT

have you tried below ?

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

Re: Pivot Table (If)

pauledrich — Tue, 11 Mar 2014 16:36:56 GMT

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?

Re: Pivot Table (If)

chiru_thota — Tue, 11 Mar 2014 18:01:04 GMT

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

Re: Pivot Table (If)

Tue, 11 Mar 2014 18:10:51 GMT

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

Re: Pivot Table (If)

pauledrich — Tue, 11 Mar 2014 18:37:16 GMT

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.