# agnostic cumsum for TS aggregation

Hey Spotters!

Problem: I'd like to create a cumsum for any different aggregation of a timeseries column.

Background: I have an activity/events/timeseries table for all the calls that come into our business and their ID (indicates their unique record ID). In order to get to the "number of calls" for a given period (hourly, daily, month ...) I simply have baked into the Worksheet calls = count(call record id). No problem.

Fast forward a bit and I wanted to create a Pin for the idiom "cumulative daily calls for a given month". My resulting formula was..

```cumulative_sum ( if ( not ( isnull ( call record id ) ) ) then 1 else 0 , day ( call datetime ) )
```

(for some reason, can't use "calls" or even count(call record id) in the cumulative_sum formula - what gives?) .. and that works all well a good as long as you also turn Month and Day into attributes.

`cumulative calls = cumulative_sum ( if ( not ( isnull ( call record id ) ) ) then 1 else 0 , day_number_of_quarter ( call datetime ) )`

Fast forward again and I've gotten to the resultset above, with basically the same cumulative sum formula, except instead of day(call datetime), now I have day_number_of_quarter(call datetime).

This is where my problem lies. Is there a way to write the cumsum without needing to know the TS aggregation beforehand?

Thanks!

- NC

3replies Oldest first
• Oldest first
• Popular
• Hi,

I tend to steer away from count and add a value I can sum as per the following table. I find this simplifies my formulas. This is dynamic on the date aggregation as I just cumulate on date.

My formulas are:

# Calls = # Calls

# Cumulative Sum = cumulative_sum (calls , date )

Month Number Search and Response

Quarter Number Search and Response

Data Table

 Call_ID Date Calls 1 1/1/19 1 2 11/1/19 1 3 1/2/19 1 4 11/2/19 1 5 1/3/19 1 6 11/3/19 1 7 1/4/19 1 8 11/4/19 1 9 1/5/19 1 10 11/5/19 1 11 1/6/19 1 12 11/6/19 1 13 1/7/19 1 14 11/7/19 1 15 1/8/19 1 16 11/8/19 1 17 1/9/19 1 18 11/9/19 1 19 1/10/19 1 20 11/10/19 1 21 1/11/19 1 22 11/11/19 1 23 1/12/19 1 24 11/12/19 1 25 1/1/18 1 26 1/2/18 1 27 1/3/18 1 28 1/4/18 1 29 1/5/18 1 30 1/6/18 1 31 1/7/18 1 32 1/8/18 1 33 1/9/18 1 34 1/10/18 1 35 1/11/18 1 36 1/12/18 1
Like
• Thanks for the response Damian Waldron . I don't think that will work in this case? Each record in the Calls table represents 1 unique call to the business. There is no column in the table called "calls" - which is why I created a formula to count the number of records.

When I go to do a cumulative_sum, my formula for calls is not allowed for some reason. Which is why I found the work around to write IF NOT NULL THEN 1 and use that value to sum.

For some reason, I never tried simply leaving aggregation part as just call datetime... which appears to aggregate just fine. I could have sworn this didn't work when I first attempted it yesterday!

Like
• Nicholas Cooper happens to me all the time. As soon as I demo the issue it magically works. PEBCAK https://en.wiktionary.org/wiki/PEBCAK :)

Like 1
Like Follow