Avg on aggr function

[kavirao]

Hi



Based on STDev  I need to calculate Avg  of categories  for each Quarter  like below.  I need to plot that Avg into line chart. Any suggessions

Categories are populated dynamically based on selection.

Percentage on Category 1 used formula of stdev

(100 - ((Sum(num([REXIST]) * num([RWeights])) -0) / (Stdev(total <QUARTER,CATEGORY> Aggr(Sum(num([REXIST]) * num([RWeights])),VENDOR,QUARTER,CATEGORY))) * FirstSortedValue(distinct ([Category Weights]),1))) /100

Now I need to calculate Avg of category in Quarter (90+100+100/3). Please any suggestions 

Company	2020Q4			AVG	2020Q3			AVG	2020Q2			AVG	2020Q1			AVG
	CATEGORY1	CATEGORY2	CATEGORY3		CATEGORY1	CATEGORY2	CATEGORY3		CATEGORY1	CATEGORY2	CATEGORY3		CATEGORY1	CATEGORY2	CATEGORY3
A	90%	100%	100%	97%	90%	100%	100%	97%	90%	100%	100%	97%	90%	100%	100%	97%
B	98%	99%	100%	99%	98%	99%	100%	99%	98%	99%	100%	99%	98%	99%	100%	99%
C	100%	95%	100%	98%	100%	95%	100%	98%	100%	95%	100%	98%	100%	95%	100%	98%
D	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%
E	98%	99%	100%	99%	98%	99%	100%	99%	98%	99%	100%	99%	98%	99%	100%	99%
F	100%	95%	100%	98%	100%	95%	100%	98%	100%	95%	100%	98%	100%	95%	100%	98%

[Lisa_Sun]

I assume the above table is the data source.  So you want to know how to have AVG measure in Qlik Sense.  Is that right?  If so, it is always best practice to generate this type of data while doing data loading.  So calculate AVG in the loading script. Or you can create master item in qvf for AVG.  So there should be four measures for Q1AVG, Q2AVG, Q3AVG and Q4AVG. 

[kavirao]

Hi lisa

table values created using below formula as measure expression. 

(100 - ((Sum(num([REXIST]) * num([RWeights])) -0) / (Stdev(total <QUARTER,CATEGORY> Aggr(Sum(num([REXIST]) * num([RWeights])),VENDOR,QUARTER,CATEGORY))) * FirstSortedValue(distinct ([Category Weights]),1))) /100