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!