Calculated field using ifelse

Question & Answer
,
1

Hi,

I’m trying to create a calculated field which requires one aggregated field and the other non aggregated in an if else statement. How can I remove error “Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination”

2

Can you move the aggregation outside of the ifelse.

instead of ifelse({field}=‘true’,sum({num_field}),…)

can you do

sum(ifelse({field}=‘true’,{num_field},0)

1 Like
3

Another option (in addition to answer provided by Max) is to change aggregation to use level-aware calculation-window function with PRE_AGG parameter.
For example instead of
ifelse({field}=‘true’,sum({num_field}),0)
use
ifelse({field}=‘true’, sumOver({numfield}, [partitionfield1, partitionfield2], PRE_AGG), 0)

You can read more details about level aware functions here Using level-aware calculations in Amazon QuickSight - Amazon QuickSight
and order of operations here Order of evaluation in Amazon QuickSight - Amazon QuickSight

1 Like