Using max value inside ifelse

Question & Answer
, , ,
1

i am trying the run the following query

ifelse(${indicadorInstitucional} = "PR", distinct_countIf(Funcional,{Soma PR} > 0 AND Ano = max({Ano})),NULL)

But it returns the following message

Nesting of aggregate functions like {{aggregateFunction1}} and {{aggregateFunction2}} is not allowed.

How could i fix the code? The code without the max works fine, i already tried to create a new calculated field using max(ano) but the error is the same

2

Hi @RafaelCardSilv,

on what level do you want to get the max value from Ano? In case you want the max level per {Soma PR} you can use:

ifelse(${indicadorInstitucional} = “PR”, distinct_countIf(Funcional,{Soma PR} > 0 AND Ano = max({Ano},[{Soma PR}])),NULL)

With this function QuickSights is requesting the maximum of Ano per Soma PR and the result is not aggregated. Then QuickSight can use it for the distinct_countIf aggregation. If Soma PR is not the right field, please change it to the correct one.

Best regards,
Nico

If this helped you, please mark my answer as solution. That helpes the community to find solutions faster.

1 Like