Countif Nesting Aggregate function

Question & Answer
,
1

Hello I am trying to do sum(value)/countif(station, limit_test = 0) However, got an error Nesting Aggregate function is not allowed.

Limit_test is also a calculated filed. maybe that’s causing the problem? but I have tried making Limit_test as calculated field from the dataset as well but showing the same error. how can I go around it? thank you.

2

Solution to this depends on what limit_tests actually is. Assuming you defined that as a sum() of something (say tests field), maybe you could try changing it to and seeing if it makes sense:

   sum(value) / countif(station, sumOver( tests, [], PRE_AGG) = 0)

But, as I said, to be 100% sure this is a correct definition please explain what limit_tests is supposed to show and how it is currently calculated.

1 Like
3

Thank you so much for your reply. I think the problem is because my TEST is also an aggregate filed

ifelse (sumover(sum(value),[station,date] > low_limit AND sumover(sum(value),[station,date] < High_limit, 0,1)

so when I only want to find AVG(value, where test = 0 ), it became nested aggregate function and throw error.

4

does it make sense to change that to

ifelse (sumover(value,[station,date], PRE_AGG) > low_limit AND sumover(value,[station,date], PRE_AGG) < High_limit, 0,1)

this way, you change the field not to include an Aggregate function and it should be ok to use it in the other field.

1 Like
5

Appreciate the reply! I tried to add PRE_AGG and got this error

for calculations level, PRE_FILTER and PRE_AGG, the operands can't be aggregated.

6

right! fixed my earlier reply now.

1 Like
7

@darcoli I got " field “Value” must be aggregated. " Error. I am trying something out. could you please take a look.

8

@darcoli so instead of filter out by using 0,1. I am doing this to get Avg(Values) that are within Low and High Limit.

ifelse (sumover(sum(value),[station,date] > low_limit AND sumover(sum(value),[station,date] < High_limit,sumover(sum(value),[station,date] ,0)

However, the avg is including 0 in calculation instead of totally omitting it . If I can find a way around to not include 0 in avg calculation, I think it will work. what do you think?