Expression not producing expected results

[zakpullen]

Hi,

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:

AGGR(Count(Distinct RotaDate)*sum(RotaWTE),RotaID,RotaMonthYear)

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.

Many thanks.

[sunny_talwar · Accepted solution]

Try this

Sum(Aggr(
    Count(DISTINCT RotaDate) * Sum(RotaWTE)
, RotaID, RotaMonthYear, [First Name], [Last Name]))

[zakpullen]

That was quick, and it works!

Thank you so much.

Zak