Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

R_Mitchell
New Contributor

Calculated Field Variance

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:

Qlik Pivot.JPG

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

1 Reply
R_Mitchell
New Contributor

Re: Calculated Field Variance

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:

Qlik Pivot.JPG

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