To check value in a calculated field

Question & Answer
,
1

Hi,
I have a requirement where I need to check value in a calculated(sum) field if > 0, then add next 2 calculated fields.

Name1 calc (sum1) calc(sum2)

if calc(sum1) > 0, then add calc(sum1) +calc(sum2) and assign it to calc3.

Can you please point how this can be achieved.

Thanks
Subbu

2

Hi @Subrjaya2

If I understand your requirement correctly, you’re trying to aggregate values and then perform an operation on the aggregated results.

For example, you’re looking to check if the sum of a field is greater than 0, and if so, add that sum to the sum of another field.

ifelse(
    sum({field1}) > 0,
    sum({field1}) + sum({field2}),
    0
)
3

Thanks, I am getting error msg when i tried that
"your calculation field expression contains invalid syntax. correct the syntax and try again.
sourceErrorCode:
CONTEXTUAL_NESTED_AGGREGATION

4

Hi @Subrjaya2

Could you please share your calculation logic.

6

ifelse(
sum({check8_20316_calc}) > 0,
sum({check8_20316_calc}) + sum({check8_20192_calc}) , 0
)

7

Hi @Subrjaya2

It looks like the fields you’re using in your calculation are already calculated fields with aggregations. Which is likely causing the NESTED AGGREGATION error.

To resolve this, try using the original fields (non-aggregated) directly in the new calculation, and apply the aggregation only once.

8

The requirement is to display aggregated value in 2 calculated fields and on the aggregated value the check needs to be performed, hence the complexity.

9

Hi @Subrjaya2

Displaying aggregated values in two calculated fields and then performing a condition check on those aggregated results introduces complexity due to restrictions on nested aggregations.

To avoid nesting while achieving the desired logic, try using the already aggregated calculated fields logic as is within the final calculated field.

Let me know the exact formulas and field names you’re using, and I’ll help restructure them accordingly.

11

fields
Name
period
code and calc fields are based on this

calc1 -Aggregate sum
ifelse({code} = “2016”, {location_value}, 0)

calc2 -Aggregate sum
ifelse({code} = “1412”, {location_value}, 0)

calc3 -Aggregate sum
ifelse({code} = “2092”, {location_value}, 0)

final calc4 field
if calc1 > 0, calc1(2016)+calc3(2092)

12

Hi @Subrjaya2

Please try the following calculation.

ifelse(
    sum(ifelse(code = 2016, {location_value}, 0)) > 0,
    sum(ifelse(code = 2016, {location_value}, 0)) +
    sum(ifelse(code = 2092, {location_value}, 0)),
    0
)

image
image715×320 9.48 KB

image
image125×107 2.2 KB

13

Thank you, it works now.