Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
R_Mitchell
Contributor
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
Contributor
Contributor
Author

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