Skip to main content
Announcements
Customer Spotlight: Discover what’s possible with embedded analytics Oct. 16 at 10:00 AM ET: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to calculate the moving difference in a Pivot Table

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

Order DateJan 1, 2012Jan 2, 2012Jan 3, 2012
RegionOrderDiff
Order
DiffOrderDiff
NY100-80-2060-20
CT35-40520-20
NJ22-331111-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!

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

6 Replies
chematos
Specialist II
Specialist II

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

Not applicable
Author

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.

nagaiank
Specialist III
Specialist III

Please see if the attached solution will be acceptable.

Not applicable
Author

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

Not applicable
Author

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

newqlik2017
Creator II
Creator II

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