topic Re: A subtraction of '1' in Constructing the expressions section p.382 in QlikView 11 For Devs book in QlikView

A subtraction of '1' in Constructing the expressions section p.382 in QlikView 11 For Devs book

happyjoshua777 — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

On p.382-3 it says the following,

"...The following expressions are constructed in the following form to obtain a
variance percentage:

(BasePeriod / CompareToPeriod) – 1

• Current Year-To-Date versus the same period last year
(Sum({$<PeriodID = {"<=$(=Max(PeriodID))"}, Year = {"$(=Max(Year))"}, Quarter = , Month = , Period = > } [#
Departures Performed])
/
Sum({$<PeriodID = {"<=$(=Max(PeriodID) - 12)"}, Year = {"$(=Max(Year) - 1)"}, Quarter = , Month = , Period = > } [#
Departures Performed]))
- 1

..."

I do not understand why '1' is subtracted if the comparison % can just be calculated via "(BasePeriod / CompareToPeriod) ".

Please let me know what this subtraction is meant to achieve.

thanks

Re: A subtraction of '1' in Constructing the expressions section p.382 in QlikView 11 For Devs book

tresB — Tue, 12 Dec 2017 05:56:35 GMT

This -1 is to get the variance (growth/degrowth) in the percentage.

Say,

your last year sales was (CompareToPeriod) =100

this year sales is (BasePeriod) =110

To get the percentage variance you would ideally use = 110/100 - 1 =1.1 - 1 =0.1

I.e. your sales variance in percentage is 0.1*100 = 10%

Re: A subtraction of '1' in Constructing the expressions section p.382 in QlikView 11 For Devs book

luismadriz — Tue, 12 Dec 2017 05:58:58 GMT

Hi,

That is telling you the percentage that BasePeriod (B) has changed from ComparePeriod (C).

Let's say C = 10 and B = 8. The variance for B from C is 20%,

Which is the same as:

(8 - 10)/10 = -0.2 = -20%

Which is the same as:

8/10 -1 = 0.8 - 1 = -0.2 = -20%

I hope this helps,

Cheers,

Luis