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
  • Newest first
  • Active threads
  • 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
    Reply 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! 😅

    Reply 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 :)

    Reply Like 1
Like Follow
  • Status Answered
  • 10 days agoLast active
  • 3Replies
  • 21Views
  • 2 Following