topic Re: adding condition to avg aggr sum in QlikView

adding condition to avg aggr sum

Marcushenriquesk — Wed, 16 Feb 2022 23:10:06 GMT

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))

Re: adding condition to avg aggr sum

Lisa_P — Thu, 17 Feb 2022 04:23:47 GMT

=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 { }

Re: adding condition to avg aggr sum

Marcushenriquesk — Thu, 17 Feb 2022 16:09:22 GMT

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

Re: adding condition to avg aggr sum

Marcushenriquesk — Thu, 17 Feb 2022 16:52:18 GMT

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

Re: adding condition to avg aggr sum

Lisa_P — Thu, 17 Feb 2022 23:58:42 GMT

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))