# Aggregate Calculation at Multiple Levels

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!

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)

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

2 Likes

Hi, @connoranderson. Did @jingyaliâ€™s solution work for you? I am marking his reply as, â€śSolution,â€ť but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community Q&A Forum!