Using ifelse and Sumif

Question & Answer
, , ,
1

I want to use ifelse and sumIf in a calculated field together but there is an aggregation mismatch error.

This is the scenario:

I want to use the ifelse to switch between parameters ${Params} and the sumIf to compute a ratio over another parameter. For instance, this is what I intend to calculate:

ifelse(
${Params} = ‘First’, sumIf(A, Date = ${SelectedDate}) / sumIf(E, Date = ${SelectedDate}),
${Params} = ‘Second’, sumIf(B, Date = ${SelectedDate}) / sumIf(E, Date = ${SelectedDate})
${Params} = ‘Third’, sumIf(C, Date = ${SelectedDate}) / sumIf(E, Date = ${SelectedDate}),
sumIf(D, Date = ${SelectedDate}) / sumIf(E, Date = ${SelectedDate})
)

Is there an alternative computation to this, or can someone help check fix the error?

Thanks a lot!

2

hi @Sarmie ,

Welcome to QuickSight Community!

for aggregation mismatch error, we have an article to explain how to resolve.

Would you be able to try to see if you can resolve the error?

3

Thanks a lot, @Wakana

I actually checked the well-detailed article earlier. The Case #3 therein seems somewhat close to my use case but not exactly, thus, I couldn’t find help there, unfortunately.

4

I used the approach below, and it seems to work fine:

coalesce(
sumIf({A}, Date = ${SelectedDate}) / sumIf({E}, Date = ${SelectedDate}) * ifelse(${Params} = ‘First’, 1, NULL),
sumIf({B}, Date = ${SelectedDate}) / sumIf({E}, Date = ${SelectedDate}) * ifelse(${Params} = ‘Second’, 1, NULL),
sumIf({C}, Date = ${SelectedDate}) / sumIf({E}, Date = ${SelectedDate}) * ifelse(${Params} = ‘Third’, 1, NULL),
sumIf({D}, Date = ${SelectedDate}) / sumIf({E}, Date = ${SelectedDate}) * ifelse(${Params} = ‘Fourth’, 1, NULL)
)

The idea is that: since multiplication with NULL is also NULL, thus removing the unwanted calculation(s). coupled with the fact that Coalesce only returns the non-null calculation per selection