Calculated Field Variance

R_Mitchell — Tue, 16 Jul 2019 15:56:32 GMT

Hi there, hope someone can help me. I am new to Qlik Sense and am learning what to do. So I have created a pivot table with 2 columns as can be seen below:

I have written an expression to give me the variance:

Sum([Sales (inc. VAT)])/Before(Sum({<[Fiscal Year]>}[Sales (inc. VAT)])) - 1

But it is also applying this to the Fiscal Year 2018/19 when I don't want it to.

Any suggestions?

Many thanks,

Rob

Re: Calculated Field Variance

R_Mitchell — Wed, 17 Jul 2019 08:36:24 GMT

Hi all,

As an update to my post yesterday I have entered the following code to turn my null values into zeros:

IF(IsNull(Sum([Sales (inc. VAT)])/Before(Sum({<[Fiscal Year]>}[Sales (inc. VAT)])) - 1),0,Sum([Sales (inc. VAT)])/Before(Sum({<[Fiscal Year]>}[Sales (inc. VAT)])) - 1)

Which has given me the following Pivot:

By doing this I assumed I would be able to then use the expression box Show column if and use something like 'Variance' <> 0, however this has no effect. Also in Add-ons and Data handling unticking the box Include zero values has no effect either. Any suggestions would be much appreciated?

Thanks,

Rob

topic Calculated Field Variance in App Development

Calculated Field Variance

Re: Calculated Field Variance