topic How to calculate the moving difference in a Pivot Table in QlikView

How to calculate the moving difference in a Pivot Table

Thu, 05 Jul 2012 20:54:45 GMT

I have a Pivot table, with Dimension 1 = Customer Region, Dimension 2 = Order Date. It looks like

Region	Order	Diff	Order	Diff	Order	Diff
Order Date	Jan 1, 2012		Jan 2, 2012		Jan 3, 2012
NY	100	-	80	-20	60	-20
CT	35	-	40	5	20	-20
NJ	22	-	33	11	11	-22

While the Expression for Order is simply =Sum(Orders), I don't know how to write the expression for the Diff column, which is the difference of the sum of the orders between the current date and the previous date.

Can anyone show me an example?

Many thanks!

Re: How to calculate the moving difference in a Pivot Table

chematos — Fri, 06 Jul 2012 00:32:13 GMT

Try this:

sum( {<[Order Date]={"=[Order Date]"},[Order Date]=>}Orders) - sum( {<[Order Date]={"=[Order Date]-1"},[Order Date]=>}Orders)

may be you want to try each one separately before

Re: How to calculate the moving difference in a Pivot Table

Fri, 06 Jul 2012 18:29:18 GMT

Thanks a lot for the help, but this doesn't work as "[Order Date]=" only disregard the filter selection, but the subset is still limited to the current dimension.

Sum(TOTAL<[Customer Region]> Orders) can get the subset that's limited only by the Region, but then {<[Order Date]={"=[Order Date]-1"},[Order Date]=>} isn't doing what I want it to do.

Re: How to calculate the moving difference in a Pivot Table

nagaiank — Fri, 06 Jul 2012 22:47:17 GMT

Please see if the attached solution will be acceptable.

Re: How to calculate the moving difference in a Pivot Table

Sat, 07 Jul 2012 11:27:37 GMT

You can use Before, to operate in pivot tables, is like Above in Straight Tables.

Try this: sum(Orders)-Before(sum(Orders))

Hope it helps

Re: How to calculate the moving difference in a Pivot Table

Mon, 09 Jul 2012 14:48:26 GMT

Thanks a lot for the help. I wasn't aware of the Before() function.

Re: How to calculate the moving difference in a Pivot Table

newqlik2017 — Mon, 02 Oct 2017 15:16:18 GMT

I'm looking for something similar but with above result. The difference column needs to a column of its own. Suggestions please?