Rolling difference
Good day,
Let's say I have integers in Column A. I'd like to generate Column B with values equal to A[n] - A[n-1].
See my example below.
How do I do that?
column A Column B
3 N/A
4 A2 - A1 = (4 - 3) = 1
9 A3 - A2 = (9 - 4) = 5
7 A4 - A3 = (7 - 9) = -2
Thanks!
-
Hi Amin,
You can achieve this with the GROUP_DIFF function:
Description
Calculates the difference between the current value and the previous value seen in the group. The optional ["initial value"] argument will be used as result for the first record. This defaults to null.
http://www.datameer.com/documentation/display/DAS50/GROUP_DIFF
I think the first example here is exactly what you're looking for.
Alan
-
Hi Alan,
GROUP_DIFF does not work for me. The error message is "Must be an aggregation function"
Let me explain more about my case:
Column A is GROUPBY(product) sorted alphabetically
Column B is GROUPBY(month) sorted ascending
Column C is GROUPSUM(balance) i.e. total balance, per product. per month.
Now, I want my Column D to be the rolling difference, based on Column C. In other words, I want to show, for each product, the difference in balance between this month and previous month.
Any idea?
-
Amin,
The issue here is that GROUPSUM is an aggregate function while GROUP_DIFF is a series function.
We'll need to create a new sheet to get around the aggregate vs series function limitation per sheet.
You'll want to do a GROUPBYBIN(month) on the new sheet, then COPY(<groupsum(balance)>). Where <groupsum(balance)> is the column on the previous sheet with your balances.
Then, you can do GROUP_DIFF on the COPY column and it should let you do it.
Give it a shot and let me know how it goes.
Alan
Please sign in to leave a comment.
Comments
3 comments