Aggregated and non aggregated in a calculated field

Question & Answer
1

Hi all!
we are having doubts in a calculated field as we want to do an aggregated level but comparing against the value of a (non-aggregated) column.
The simplified calculation would be like this:
ifelse({FIELD}= ‘A’,
sum({CALC_NUM}),
avg({CALC_NUM})
)
Here ‘FIELD’ is a text field and we want that depending on its value it sums or averages for a measure.
We get the following error:
A grouping function is missing. Custom aggregations cannot contain both aggregation “SUM” and non-aggregation “SUM(“CALC_NUM”)” fields in any combination.

What are we missing? I would appreciate any help to enlighten us.
Many thanks

2

Can you try wrapping the if statement in an aggregation?

ifelse(firstValue({FIELD},[{FIELD} ASC],[{FIELD}}])=‘A’,sum({CALC_NUM},avg({CALC_NUM}))

Let me know if that works

1 Like
3

Yes! it’ works, many thanks!

4

Hi,

this structure work, but here we have to add the field in the view, right?! otherwise we get an error message.

you can hide the field, but the rows will still show, only the field values are gone.

is there a way to apply a similar statement without having to include the field in the view?