Use calculated field as a variable

Question & Answer
1

Hello everyone.
I have the following calculated field, which is called “calculated_value”:

sum({column_1})/sum({column_2})-1

I want to use it in the following,second, calculated field:

sum(ifelse(isNotNull(column_3), column_3, column_4 + (column_4 * calculated_value))

The first calculation works fine. The second one, however, raises a “nesting of aggregate functions not allowed” error.

How can I use the “calculated_value” in another calculation? All I want to do is store a value as a variable and use it in a calculation.

Thanks for the help.

2

normally, you can use a calculated field in another calculated field. Unfortunately, it’s the sum()/sum() functions that are preventing you from doing what you want to do, the two sum functions are the aggregations that are causing the error message.

Perhaps if you could better explain what you are doing with all four columns, someone else smarter than me could find a solution for you. I’m sure if you replace “calculated value” in your second calculation with the sum()/sum()-1, your second calculation should work.

3

Hey! Thanks so much for the quick reply.

I’ve tried replacing as you suggested, and the error continues.

I’m migrating a huge query from metabase to quicksight that uses aggregations inside subqueries that have a filter. As we can’t alter the query with filter inputs, I’m bringing all of the data and letting quicksight filter it. Of course, the aggregations results inside the subqueries differ from filtering inside the subquery and outside.

As the problem is kind of complex, I’m trying to simplify it by stating that all I want to do is:

  • Calculate a value
  • Store this value as a raw number
  • Use this value inside a calculation

I assume this must be doable somehow…
Thanks again =)

4

Try simplifying it, take out the sum, I’m pretty sure Quicksight will aggregate it on its own. Sometimes you have to play around with the syntax to see what works. It really helps as well to line up your brackets.

ifelse(
isNotNull(column_3), column_3,
column_4 + (column_4 * calculated_value)
)

5

Hey. Thanks for the reply!

I can’t take out the sum. It raises the following error:
“Mismatched aggregation. Custom aggregations can’t contain both aggregate (SUM) and non-aggregate fields in any combination”.

7

Sum column_3 and column_4 and don’t sum calculated value.

ifelse(
isNotNull(column_3), column_3,
column_4 + (sum(column_4) * calculated_value)
)

However, the one remaining issue is the check of isNotNull on column three.

Can you explain more on what the ideal visual looks like?

Are you saying that if a row has column_3 value then show that value?

Do you need sums in your calculated value?

Could you do

calculated value = {column_1}/{column_2} -1

And then sum(ifelse(isNotNull(column_3), column_3, column_4 + (column_4 * calculated_value)) ?