Hey. I'm looking to create a cohort analysis table. I found this article, but it doesn't mention how to do a RUNSUM. Basically I need to do a running count (current idea is to do

RUNSUM(COUNT())

) based on 2 dates. When I do this runsum MAQL statement, it breaks because RUNSUM can only accept 1 date.

Hi Filip, The runsum function indeed only works with a singular date dimension. Could you provide some more details on your use case? How vital is it for you to use two date dimensions within the metric? Could you filter through a dashboard filter?

Ideally I want something like this - we need to track 2 dates because 1 date is the received date and the 2nd date is the updated date. The % values in the pic show a cumulative sum of updated claims / total claims submitted that day (based on submission date)

Interesting use case. I tried to solve it in my Github analytics project - calculate it on top of "commits" using created/updated date:

SELECT (
  SELECT COUNT({label/commit_id}) BY {label/created_at.day} ALL OTHER
) / (
  SELECT COUNT({label/commit_id}) BY {label/updated_at.day} ALL OTHER
)

If I slice it by created date, it "works", see screenshot. 1. It does not use RUNSUM, but it should be possible to extend it accordingly 2. It slices by created date. Not 100% sure if it is what you need, but generally I don't see other way how to calculate something meaningful

The RUNSUM has currently limitation that it works only over a single date dimension. You may consider workarounds such as use of regular text attribute for definition of cohort or use multiple metrics broken by one dimension where each metric would apply different filter using the other date dimensions. You can put up to 20 metrics to insight as columns and use date for rows or transpose the table (20 rows as metrics and columns for breakdown by date)

I don't mind creating many metrics @Jakub Sterba - but updated date should be greater than submitted date in increments of 1. Any idea how to achieve this? I dont think you can do updated_date = submitted_date + 1 can you?

I am not sure if I understand. You may want to create special dimension in the data model for this like "Days since submission" and then build the cohort analysis using it, but such attribute cannot be used for cummulative counts. We are considering to offer cumulative count at insight level on top of MAQL metrics which may probably help here in future.

Ok, it seems doing days since submissions helped. Now I am having trouble doing a runsum. I tried using BY, WITH, FOR EACH, WITHIN - but everything gives me the 2nd option in pic attached. This is my latest trial using for each

SELECT RUNSUM(SELECT COUNT({label/gooddata_main_data.claim_id})
where {label/open_claim_status_map.open_claim_status} = "Missing or Incorrect Info" 
OR {label/open_claim_status_map.open_claim_status} = "No Claim on File" 
OR {label/open_claim_status_map.open_claim_status} = "Processing")
FOR EACH {label/open_claim_status_days_since_submission}

This is because RUNSUM works only over dates. You can create the days since submission as date attribute (e.g. values like 2024-01-01, 2024-01-02, 2024-01-03 which would be displayed as Day of Year = 1,2,3) but you would have to use on rows text attribute (table column would have to be mapped to attribute in LDM in addition to mapping to date dataset) and this attribute would have to be used for rows of table. I admit this is not nice workaround. Alternative workaround for 20 columns can be to define separate metric for each column. E.g. column1:

select {metric/count_metric} where {label/days_since_submission} in ("1")

column2:

select {metric/m} where {label/days_since_submission} in ("1","2")

column3:

select {metric/m} where {label/days_since_submission} in ("1","2","3")

...

The titles of columns for metric or attribute title in rows can be renamed on level of insight so you can display just numbers in table header on top (1,2,3…) instead of names of metrics.