# Level of aware calculation question

Hello, I’m trying to calculate a ratio - OTP for all the partners.

For example, i have 2 partners p1&p2 ,
P1 OTP 60.3%
P2 OTP 76.2%
The ratio i would like to get is (60.3%+76.2%)/2= 68.3%.

I was able to get this ratio by using this formula, but i have to show the vendor alias in my table and I would like to get the overall OTP ratio for partners without showing certain vendors.
`sumOver(OTP, [Quarter])/countOver(distinct_count({vendor_alias}),[Quarter])`

`sumOver(OTP, [Quarter], PRE_AGG)/countOver(distinct_count({vendor_alias}),[Quarter], PRE_AGG)`

1 Like

hi, i got this error: For calculation levels PRE_FILTER and PRE_AGG, the operands can’t be aggregated

Try this:

sumOver(OTP, [Quarter], PRE_AGG)/distinctCountOver({vendor_alias},[Quarter], PRE_AGG)

If this still doesn’t work, can you confirm if OTP is a calculated field? If so, can you show its calculation?

unfortunate i got the same error but my OTP Formula is {otp_numerator}/ {otp denom}

Are {otp_numerator} and {otp denom} calculated fields?

1 Like

Yes they are;
otp_num: `distinct_count(ifelse({otp_root_cause} = 'On-Time',{Offer Published},NULL))`
otp_denom:`distinct_count({Offer Published})`

That’s the issue. That error message is basically saying you can’t put distinct_count inside discountCountOver, so you need to calculate otp_numerator and otp_denom differently.

If you can show a sample of your raw data (before doing any aggregations), there may be an easier way to get to the result you’re looking for.

1 Like

I hope this makes sense, thanks for your help

Can you try this?

otp_num =
distinctCountOver(ifelse({otp_root_cause} = ‘on time’, {Offer Published}, NULL), [{vendor_alias}], PRE_AGG)

otp_denom =
distinctCountOver({Offer Published}, [{vendor_alias}], PRE_AGG)

OTP =
avg(avg({otp_num}/{otp_denom}, [{vendor_alias}]))

In your screenshot shouldn’t apple be 2/3 = 67%?

1 Like

thank you so much , it works! i’m trying to get the period over period percent diff, my old formula was `periodOverPeriodDifference({overall otp},{avails_start_date},QUARTER,1),` but it shows the operation related to Level Aware Calculation (LAC) agg function is not supported, any idea what other formula i can use? thanks

1 Like

yes date format is YYYY-MM-DD(Ex: 2023-02-24), I used aggregate function to get quarter, month and week number because I have quarterly, monthly and weekly view

This is a lot more complicated. You’ll need a whole bunch of new calculated fields. Keep the original calculated field for the overall OTP and create these new ones:

OTP_Num by Vendor and Quarter =
distinctCountOver(ifelse({otp_root_cause} = ‘on time’, {Offer Published}, NULL), [{vendor_alias}, Quarter], PRE_AGG)

OTP_Denom by Vendor and Quarter =
distinctCountOver({Offer Published}, [{vendor_alias}, Quarter], PRE_AGG)

OTP by Vendor and Quarter =
{OTP_Num by Vendor and Quarter}/{OTP_Denom by Vendor and Quarter}

OTP by Row =
{OTP by Vendor and Quarter}/countOver({Offer Published}, [{vendor_alias}, Quarter], PRE_AGG)

OTP by Quarter =
sumOver({OTP by Row}, [Quarter], PRE_AGG)/distinctCountOver({vendor_alias}, [Quarter], PRE_AGG)

OTP QoQ Difference =
periodOverPeriodDifference(avg({OTP by Quarter}), Quarter)

1 Like

@David_Wong sorry for the late response, but I think there’s some issue with this calculation, in my screenshot, first column is vendor_alias, and this is Q2 2023 data. the correct OTP by partner formula should be (100%+20%+100%+59.25%+0%)/5=55.8%

but in quicksight, the OTP by Vendor and Quarter formula give me 60.94%, do you know which part was wrong?

This isn’t giving you the correct result because now you want the average per quarter. It’s giving you the average across all quarters.