How to calculate difference between aggregation and parameter using condition

Question & Answer
, ,
1

Hello! I’m trying to calculate the difference between a parameter (static value) and an aggregation, using a condition to select the right parameter. Specifically, I’m trying to calculate budget remaining by grouping spend (aggregation) and subtracting from budget ceiling (parameters). Example below:

ifelse({budget name} = ‘1’, ${budget ceiling 1} - sumOver(sum({spend}), [{budget name}]),
{budget name} = ‘2’, ${budget ceiling 2} - sumOver(sum({spend}), [{budget name}]), NULL)

I keep getting ‘Mismatched aggregation. Custom aggregations can’t contain both aggregated and non-aggregated fields, in any combination.’ error.

Please help!! Thanks in advance!

2

Hello @mkleeman, welcome to the QuickSight Community! I would suggest splitting this up into 3 calculated fields then it should work how you expect.

Create a calculated field with just the sumOver function and create another with the ifelse to return either ${budget ceiling 1} or ${budget ceiling 2} depending on your {budget name} field.

Your final calculated field which you will add to your visual with subtract the run the subtraction function. That should resolve your aggregation issue and give you the desired output!

3

Hello @DylanM, thank you for the warm welcome and your assistance!

I tried splitting this into multiple calculated fields, and ran into a syntax error with the ifelse returning budget parameters. See sample syntax below:

ifelse({budget name} = ‘1’, ${budget ceiling 1}, {budget name} = ‘2’, ${budget ceiling 2}, {budget name} = ‘3’, ${budget ceiling 3}, {budget name} = ‘4’, ${budget ceiling 4}, {budget name} = ‘5’, ${budget ceiling 5}, {budget name} = ‘6’, ${budget ceiling 6}, {budget name} = ‘7’, ${budget ceiling 7}, {budget name} = ‘8’, ${budget ceiling 8}, NULL)

Looking forward to hearing your thoughts.

4

Hello @mkleeman, is your budget name field a calculated field? If so, are you running any aggregations in the field?

I have tested this on my end, and everything seems to be running as expected. I have a calculated field that checks if a value falls into a grouping to create a budget name type of field that returns string value numbers.

Then I run another ifelse that matches what you have above to return budget ceilings based on the budget name value. The parameters I set are static integer values. Let’s call this {budget_ceiling}

I then made the sumOver function, we can call this {sumOverSpend}

My last calculated field runs this: {budget_ceiling} - {sumOverSpend}

In a table display, I average the last value. It persisted on multiple rows for me, so I wanted one row value since it takes into account the totals. You could also use min or max.

My best thought is there is an issue with your {budget_name} field or the value type of your parameters.

5

Hello @DylanM, yes, budget name is a calculated field. And the data type is string.

6

Hello @mkleeman, are you able to show me how you created the budget name field? Anonymize any data that may be sensitive in the calculation, I just want to see how it is made.

7

Sure! Please see below:

left({business justification}, 4)

8

Hello @mkleeman, it might be worth trying to create that budget name field directly in your SQL. I can’t think of any reason it isn’t working unless for some reason the function in that calculated field is not playing well in the ifelse statement or the values do not match. Double check that you are using the correct names in your ifelse, if so run the SQL alternative to that calculated field function in your SQL query for the dataset and it should all work as expected.

I am going to mark this as the solution for now, but if these steps do not resolve the issue you are facing please let me know!