Rolling difference



  • Alan Mark

    Hi Amin,

    You can achieve this with the GROUP_DIFF function:


    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.

    I think the first example here is exactly what you're looking for.


    Comment actions Permalink
  • Amin Torabi

    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?

    Comment actions Permalink
  • Alan Mark


    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.


    Comment actions Permalink

Please sign in to leave a comment.