I have data similar to the fake table below. The start and end dates are the period for which a doctor is assigned to a RotaID. For any given month (MonthYear) I want to aggregate wte * rotadays for each RotaID. Rotadays are the number of days a doctor is assigned to a RotaID in the given month. For example, rotadays for May 2020 in the first row would be 4.
I have a calculation that works fine if there is a single doctor against a RotaID in a specific month:
However it doesn't work if 2 doctors are against a RotaID in the same month. If we take RotaID Anaes SPR2 and August 2019 as an example.
The correct calculation should be:
Michael Wood: 31 * 0.7 = 21.7
Michelle Brown: 25 * 0.8 = 20
The expression should return 41.7 (21.7 + 20)
However, it is not performing each calculation seperately, and aggregating the results. It is first adding the wte together (0.7 + 0.8 = 1.5), and then multiplying that by the first count of rotadays (31) to produce 46.5.
I've experimented with my expression, but just cannot nail it. I tried including a RowID, but that didn't work.
Is there a solution? I don't think this can be done in the script as a calendar is created, and an interval table used to link it to the data table.