Level Aware Aggregate Calculation week average

[Iris_Zhou]

hi QS community,

I’m trying to match the otp% partner average number in this 2 views.
For the week of dec 3, 2023 the partner average is 47.7% ( it’s the sum of otp% -offes average / total count of vendors that week)

but in my weekly view, the number only match if i only select that week. if i have more weeks, the number won’t match. (ex: showing 48.3% in screenshot)
My current formula is otp num :distinctCountOver(ifelse({otp_status} = '1', {unique_key}, NULL), [{vendor_alias}], PRE_AGG)
otp denom: distinctCountOver({unique_key}, [{vendor_alias}], PRE_AGG)
otp% partner average = avg(avg({otp num partner}/{otp denom partner}, [{vendor_alias}]))

the other way I tries is using sum(otp)/count vendor week. but the sumotp week is not correct, the formula i used is sumOver(OTP,[WEEK])

any help would be appreciated.

[David_Wong]

Hi @Iris_Zhou,

Have you tried adding WEEK to the partition in each of your calculated fields?

otp num:

 distinctCountOver(ifelse({otp_status} = '1', {unique_key}, NULL), [{vendor_alias}, WEEK], PRE_AGG)