adding condition to avg aggr sum

[Marcushenriquesk]

hello i have a script like this =avg(aggr(Sum(RUN_DATE_TIME-RECEIVED_DATE_TIME)*24 ,ACC_ID)) for a line graph measure. i am trying to add a condition to filter some of the data in it. i tried =avg(aggr(Sum(if(TEST_ID = '1000',RUN_DATE_TIME-RECEIVED_DATE_TIME)*24 ,ACC_ID))) but its giving me a garbage error. what would be the proper syntax? here is the original script. =avg(aggr(Sum(RUN_DATE_TIME-RECEIVED_DATE_TIME)*24 ,ACC_ID))

[Lisa_P]

=avg(aggr(Sum({<TEST_ID={1000}>}RUN_DATE_TIME-RECEIVED_DATE_TIME)*24 ,ACC_ID))

This uses Set Analysis to limit the set of data this expression is applied to within the { }

[Marcushenriquesk]

perfect, how would a range work for example (test_id >= 10 and Test_ID <=1000) and (Test_id >= 1500 and Test_id <=2500)

[Marcushenriquesk]

or also the 'not in' clause is e() right? if i were to make a range how would i implement that so that it would be all tests that are not in that range using set anaylsis

[Lisa_P]

For a range (assuming you want the OR combination between the ranges:

=avg(aggr(Sum({<TEST_ID={">=10 <=1000"}+{">=1500 <=2500"}>}RUN_DATE_TIME-RECEIVED_DATE_TIME)*24 ,ACC_ID))