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])

Can you try this instead?

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)

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

Can you add the date field to your sample data?

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.

Can you try this instead?
avg(avg({otp_num}/{otp_denom}, [{vendor_alias}, {quarter}]))

1 Like

@David_Wong the number itself seems correct for individual quarter, for example q2 2022, i got 77.14%, but when i remove the date filter that shows all the quarter data, the table below shows q2 2022 is 75.83%, do you know why did that happen?

To troubleshoot, I suggest applying a filter to remove all vendors except for two from your analysis, and then looking at the calculated average to understand why QuickSight is giving you that value. That’s what I usually do when faced with similar issues. Apply filters to reduce the amount of data in the visual to make troubleshooting easier.

1 Like