QuickSight Calculated Field Error

Q&A
,
1

Hello, I am trying to create an ifelse statement that has a couple steps to it. It goes like this:
ifelse({stay_date} >= ${p1Start} AND {stay_date} <= ${p1End},
((sum(example_a) /sum(example_b)) /
(sum(example_c) / sum(example_d))* 100), 0).

I am getting an error of: “Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination.”

I know I could do that calculation with a filter instead of trying to use an ifelse, but I am trying to do the same thing for p2Start and p2End and then find the difference between the two by doing calculated field 1 - calculated field 2. I could be on the wrong track, so anything will help.

2

Hi @Lwojo ,

Welcome to the QuickSight Community and thanks for posting your question.

The reason you are getting a “Mismatched aggregation” error is because the if condition isn’t aggregating the {stay_date} field, but the calculation is using the sum() aggregation.

So for example, if you changed the calculation to:

ifelse(
min({stay_date}) >= ${p1Start} AND max({stay_date}) <= ${p1End},
((sum(example_a) /sum(example_b)) /
(sum(example_c) / sum(example_d))* 100), 0)

then you will not receive the error, but this may not result in the desired values.

If you can share some sample data with the expected result then it may help find a solution.

Regards,
Andrew

3

Hey Abacon,

This was close to what I needed, but it wasn’t exactly the desired results. What I had to do was calculate example a, b, c, and d first for p1 individually. Then I was able to go ahead and do the equation.

Thank you!

1 Like