Search code examples
google-sheetsgoogle-query-languagegoogle-sheets-query

Cumulatve Difference or rolling difference in Google Sheets - Query

I have the following query formula that pivots the follower count of different facebook pages from one sheet to another:

=QUERY('Page Level'!A2:N, "SELECT C, MAX(J) where J<>0 and M<>'' GROUP BY C PIVOT M label C 'Date'")

the result is something like this but with more countries:

Date    Australia   Austria Belgium
2018-01-01  7912    4365    1343
2018-01-02  7931    4364    1343
2018-01-03  7930    4366    1344
2018-01-04  7928    4365    1345
2018-01-05  7929    4362    1347
2018-01-06  7939    4363    1347
2018-01-07  7950    4361    1348
2018-01-08  7933    4339    1343

I would like, instead of having the full follower count, a simple difference between dates. So, if we take the table above the result would be something like this:

    Date      Australia Austria Belgium
    2018-01-01  7912    4365    1343
    2018-01-02  19       -1     0
    2018-01-03  -1      2       1

and so on for each new date. Anybody knows how to do this on google sheets by any chance?

If it helps, I also have the data whereby the countries are all in the same column. However, the data is not ordered by country and by date. Rather by date and country so this solution will have to sort the data somehow beforehand I imagine.

Pivoted Data:

Date    Country         Followers
2018-01-01  Australia   7912
2018-01-01  Austria     4365
2018-01-01  Belgium     1343
2018-01-02  Australia   7931
2018-01-02  Austria     4364
2018-01-02  Belgium     1343
2018-01-03  Australia   7930
2018-01-03  Austria     4366
2018-01-03  Belgium     1344
2018-01-04  Australia   7928
2018-01-04  Austria     4365
2018-01-04  Belgium     1345
2018-01-05  Australia   7929
2018-01-05  Austria     4362
2018-01-05  Belgium     1347
2018-01-06  Australia   7939
2018-01-06  Austria     4363
2018-01-06  Belgium     1347
2018-01-07  Australia   7950
2018-01-07  Austria     4361
2018-01-07  Belgium     1348
2018-01-08  Australia   7933
2018-01-08  Austria     4339
2018-01-08  Belgium     1343
Solution

  • I have got fairly close to it using the basic idea of subtracting the pivoted data from the pivoted data offset by 1:

    =iferror(arrayformula({query(A:C,"SELECT A, MAX(C) where C<>0 and B<>'' GROUP BY A PIVOT B limit 1 label A 'Date'");
query(A:C,"SELECT A, MAX(C) where C<>0 and B<>'' GROUP BY A PIVOT B offset 1")-
query(A:C,"SELECT 0, MAX(C) where C<>0 and B<>'' GROUP BY A PIVOT B")}),"")

    It produces an error (as you would expect) where it tries to subtract the header and later a non-existent row. I don't know how to avoid this except by using IFERROR which produces a blank line as below:

    enter image description here