Calculated Field Error for Creating Yes/No Flag

[yipk]

Hi QuickSight Community,

I would like to create a Yes/No calculated field where it determines when stacking location contains “-FLAT” and the average package count is less than 250, it would be marked as “Yes”, else “No”.

I created a calculated field with the following formula:
ifelse(contains({stacking_location}, ‘-FLAT’) AND avgOver({pkg_count}, [{sort_center}, {parent_stacking_filter},{stacking_location}]) < 250, ‘Y’, ‘N’)

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

I was wondering if anyone happened to know how I can get it to work. Thank you!

[duncan]

Hello @yipk !

I would recommend moving the avgOver calculation to its own and retrying this calculated field:

avgOverCalc = avgOver({pkg_count}, [{sort_center}, {parent_stacking_filter},{stacking_location}])

ifelse(
contains({stacking_location}, ‘-FLAT’) = 'True' AND 
avgOverCalc < 250, 
‘Y’, 
‘N’)

I also recommend checking out this blog post from one of the community experts:

Level-Aware Calculations: Tips and Best Practices

[Level-Aware Calculations in Amazon QuickSight] Level-Aware Calculations (LAC) are powerful functions that allow us to perform calculations at a granularity level which is different from the granularity of our datasets and/or visuals. The sample dataset referenced by this article is a dataset of product orders placed by customers. Each order can contain multiple products and each product in an order has a unique Line Item ID. Each row of our dataset represents one line item but we want to per…

Let me know if this helps!

[yipk]

Hi @duncan , thank you for your response!

I gave your two calculated fields a try, but I am still getting the same error of "mismatched aggregation. Custom aggregations can’t contain both aggregated and non-aggregated fields, in any combination.

I have attached a screenshot for reference.

[duncan]

Hey @yipk !

avgIfFlat =

avgOver(
ifelse(contains({stacking_location}, ‘-FLAT’), {pkg_count}, NULL),
[{sort_center}, {parent_stacking_filter},{stacking_location}])

Then a second calculated field would be:

 ifelse(
{avgIfFlat} >= 250, 
'Yes', 
'No')

You may have to experiment with the Null value to get this to work.

[yipk]

Hi @duncan !

I gave the avgIfFlat a try, but it appears to not be working. I get an error as shown below:

For more context, this is the pivot table that I am trying to apply the flagging logic on:

I would like to flag the -FLAT stacking locations when the average package count is less than 250.

[duncan]

Hey @yipk !

I think you should try breaking these both up and then apply it to an ifelse statement.

Change avg calc to this: avg({pkg_count}, [{sort_center}, {parent_stacking_filter},{stacking_location}])

Calc 1 = ifelse(contains({stacking_location}, ‘-FLAT’) = True, 1, 0)
Calc2 = ifelse({avgIfFlat} >= 250, 1, 0)

Calc3 = ifelse({Calc1} + {Calc2} = 2, 'Yes, 'No')