topic Re: Difference formula in QlikView

Difference formula

Fri, 16 Oct 2015 19:34:18 GMT

Hello,

I have one column (dimension) Year

and in the rows i have the months e.g.

2015 diff. 2014 diff

oct 10 -40 50 -

sept 20 -20 40 -

aug 30 0 30 -

jul 40 20 20 -

Difference is the amount of that year in relation to the year before.

e.g. 2015 is 10; 2014 is 50; difference between both is -40

what is the formula for the difference column?

Re: Difference formula

sunny_talwar — Fri, 16 Oct 2015 19:36:47 GMT

Which type of object are you using this in?

Re: Difference formula

Fri, 16 Oct 2015 19:38:47 GMT

pivot table

Re: Difference formula

sunny_talwar — Fri, 16 Oct 2015 19:47:10 GMT

This?

Script:

Table:

LOAD *,

Month(Date) as Month,

Year(Date) as Year;

LOAD * Inline [

Date, Value

07/01/2015, 40

08/01/2015, 30

09/01/2015, 20

10/01/2015, 10

07/01/2014, 20

08/01/2014, 30

09/01/2014, 40

10/01/2014, 50

];

Pivot Table:

Dimension: Month, Year

Expressions:

1) =Sum(Value)

2) =Sum(Value) - After(Sum(Value))

Sorting is descending for both the dimensions (this is required for After() function to work. If you decide to change the sorting, change the After() function to Before() function.

HTH

Best,

Sunny

Re: Difference formula

Fri, 16 Oct 2015 20:16:09 GMT

Hello,

Do you also know the answer if the difference column would be the cumulate.

e.g.

2014 No Cum

oct. 10 100 (90 + 10)

sept. 20 90 (70 + 20)

aug. 25 70 (45+25)

july. 45 45

Thx in advance