Average by Dimension

ahudache — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I'm looking to compute the monthly average count within a pivot table. Below is an example of the pivot. I'm able to create the Count section, but anytime I try to incorporate the Monthly Avg, it doesn't let me compute the average of a COUNT by the dimension (which is MONTH). I believe I should be using RangeAvg, but haven't been able to get it to work properly.

Dimension:Month

Expression: count(ID)

	Count				% of Monthly Avg
ID	Jan	Feb	Mar	Monthly Average	Jan	Feb	Mar
1	720	626	633	660	109%	95%	96%
2	598	604	460	554	108%	109%	83%
3	384	452	330	389	99%	116%	85%
4	401	466	302	390	103%	120%	78%
5	138	134	1,086	453	30%	30%	240%

Ultimate Goal: I would like to highlight the count cell that is > 150% of the monthly average. Eg: ID=5, the counts jumped to 240% of what the monthly average usually is.

Any help is much appreciated!

Thanks,

Andrew

Re: Average by Dimension

swuehl — Mon, 27 Nov 2017 20:39:06 GMT

Maybe something like this as pivot table expression for your monthly average:

=Avg(TOTAL<ID> Aggr( Count(ID), ID, Month))

topic Average by Dimension in QlikView

Average by Dimension

Re: Average by Dimension