quicksight sum ifelse calculation question

Question

Hi all, I want to create a calculated field named %, my sql logic is case when metric name in (offer_count, title_count) then num else (num/denom),

I have tried ifelse(in({metric_name},["OTD %","OTP %","Storefront Availability %"]),sum({metric_numerator})/sum({metric_denominator}),sum(metric_numerator)) . but quicksight doesn't support mixed aggregation in ifelse.

I also tried sum(metric_nominator)/ sum(ifelse(in({metric_name},["title_count","offer_count"]),1,{metric_denominator})), i thought i could assign the denom as 1 for offer/ title count, but it sums the denom so is no longer 1. any suggestion how can i get the title/offer count part to show the number only? I can also change the data structure if there's a better way to do this

Answer 1

Does this work?

ifelse(
    in({metric_name}, ['a', 'b', 'c']),
    sumOver(numerator, [content_value, metric_name], PRE_AGG)/sumOver(denominator, [content_value, metric_name], PRE_AGG),
    sumOver(numerator, [content_value, metric_name], PRE_AGG)
)

I added the partition bys for each SumOver so that the custom field works for your table, where it looks like you are grouping by content_value and metric_name.