Mismatched aggregation. Custom aggregations can’t contain both aggregate "`NESTED_COUNT`" and non-aggregated fields

Q&A
, , , , ,
1

Hey all,

I am using a count function to get data after grouping certain columns in my dataset as below:

count({destination_type}, [room, {position_name}, {destination_type}])

and later on using another calculation that contains ifelse as follows

ifelse({power} = ‘5’, {count_per_destination}/2, {count_per_destination})

The first calculation works fine but the second one is giving me an error of mismatched aggregations. I need both columns separately. So the column that comes out of the first calculation and the column that comes out of second calculation.

Any help appreciated. Thanks.

2

Welcome @Daniyalali2010,
looks to me like you are grouping the count (aggregated) but the {power} is on row level (non-aggregated).
BG

3

That’s correct. But how can I resolve this? I need the condition of if to be on power but the action needs to happen on the aggregated columnn

Thanks.

4

btw is power a string or integer?

5

Power is a string in this case

6

If you change your count to countOver, I think it should work.

countOver({destination_type}, [room, {position_name}, {destination_type}], PRE_AGG)

countOver doesn’t group your rows, so QuickSight will be able to evaluate the condition at the row level.

7

This worked. Thanks David!