Aggregate Calculation at Multiple Levels

Question and answer
#1

Hi,

I would like to perform an aggregate showing sum of minutes for every patient in my dataset for the current month, previous month, and month before previous. I have learned how to sum minutes by patient, but how can I get a field to show sum of minutes by patient for previous month?

In my dataset every row represents an interaction, is associated with a patient and date, and has a duration (time). I have variables called IsCurrentMonth, IsPreviousMonth, and IsPreviousPreviousMonth, which give each row either a 1 if the interaction occurred in the specified month, or 0 if not. These are dynamic, using now() function to update as the month changes.

Ultimately we’d like to give patients an index based on how many minutes they have had over the past three months. 0-5 mins in a month would score 1, 5-15 is 2, 15-20 is 3, and 20+ is 4. So for patient 1 if they had 4 mins two months ago, 10 mins for the previous month, 16 mins for this month, their index score is 6. This is why I figure sum of minutes for previous previous month, sum for previous, and sum for current all need to be their own calculated fields so I could use them in an ifelse later.

Any help is appreciated, thanks!

#2

Hey there,

Let me try to provide a solution

Input dateset:

patient,date,duration
A,2022-08-01,10
A,2022-07-01,5
A,2022-06-01,1
A,2022-08-01,100
A,2022-07-02,50
A,2022-06-02,10
B,2022-08-01,20
B,2022-07-01,30
B,2022-06-01,40
B,2022-08-01,25
B,2022-07-01,35
B,2022-06-01,45

Create below Calculated Fields:

MonthDiff = dateDiff(date, now(), "MM")

Sum of Duration per Customer per Month = sumOver(duration, [patient, MonthDiff], PRE_AGG)

Count of Records per Month per Patient = countOver(patient, [patient, MonthDiff], PRE_AGG)

// LAC-W will create duplications, so we need to dedup below. 
Score per Month per Patient = ifelse({Sum of Duration per Customer per Month} > 20, 2, 1) / {Count of Records per Month per Patient}

Score per Patient = sumOver({Score per Month per Patient}, [patient], PRE_AGG)

Score per Patient should be the final result. (Note: Using min for it in Visual aggregation)

image (2)
image (2)966×393 8.86 KB

sumOver and countOver are LAC-W functions. More details can be found here

Also, for Score, we can use nested ifelse like a case statement:

ifelse
(
    {Duration} < 5, 1,
    {Duration} < 10, 2,
    {Duration} < 20, 3,
    4
)

Hope this can help :slight_smile:

2 Likes