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!