Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination

Question & Answer
, ,
1

I’m getting the following error in the formula below: Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination.

Processed Records Weekly Avg (Cap Period):

{Processed Records Within Cap Period}/{Cap Contrac In Force Weeks}

Which combine both calculated metrics below:

Calculated field: Processing Records Within Cap Period

distinct_countIf(id, type2 <> “integrate” AND type2 <> “process” AND createdts > {Contract Start date (Licence)} AND createdts <= {Contract End date (Licence)})

Calculated field: Cap Contrac In Force Weeks

dateDiff({Contract Start date (Licence)},{Current Date},“WK”)

Wondering if you have any suggestions, please? Thanks.

2

Managed to get some help from a college, will add his suggestion here in case someone else has the same issue:

The reason for the error is that one of the fields you are using in the calculation has been aggregated (distinct_count) whilst there is no aggregation being applied to the datediff field hence the error is saying you can use both in a calculation. They either need to both be aggregated or not aggregated. To solve this I would aggregate the Cap Contrac In Force Weeks either in the final calculated column or within this one (i.e. wrap the datediff in an aggragation). You’ll probably want to use one of max/min/avg instead of count or sum.

How the calculated field was with error:

{Processed Records Within Cap Period}/{Cap Contrac In Force Weeks}

How the calculated field is now, working:

{Processed Records Within Cap Period}/avg({Cap Contrac In Force Weeks})

3

Hi @jessica.lunelli, welcome to the QuickSight Community. Here are some more useful resources:

Thanks for your contribution.