Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I need to work out how to build an expression which works out the average values over a period of time and then counts that number of these which are over target and the number which are below target. I was looking to build this in a gauge chart but I'm not fussy if this is not possible. I guess the crux of it is how do you structure an expression so that the later part calculates based on the results of the earlier part?
Please, can anyone help me?!
Thanks,
Rachel
Rachel,
You need advanced aggregation to accomplish this (aggr). It's difficult without having sample data to work with, but the general format would look like this: sum(aggr(if(avg({<Date={">=$(vStartDate<=$(vEndDate))"}>} Value)>vTarget,1),dimension)) where vStartDate and vEndDate are variables containing your period start and end dates, respectively, and vTarget is your target variable. This will work in a gauge.
Regards,
Rachel,
You need advanced aggregation to accomplish this (aggr). It's difficult without having sample data to work with, but the general format would look like this: sum(aggr(if(avg({<Date={">=$(vStartDate<=$(vEndDate))"}>} Value)>vTarget,1),dimension)) where vStartDate and vEndDate are variables containing your period start and end dates, respectively, and vTarget is your target variable. This will work in a gauge.
Regards,
Thanks - just what I needed!