topic Re: Sample Variance over a Dimension in QlikView

Sample Variance over a Dimension

Wed, 25 Nov 2020 16:16:04 GMT

Trying to get a Sample Variance Calculation working in Qlik on a data-set similar to the one below. Having an incredibly tough time getting the

Formula:

Sample variance : s^2 = Σ ( xi - avg(x) )^2 / ( n - 1 )

Data-Set

DIVISION	STAFF_ID	SALES_SUM
NORTH	100	$50.00
NORTH	110	$350.00
NORTH	111	$350.00
NORTH	112	$350.00
NORTH	113	$350.00
NORTH	114	$320.00
NORTH	115	$500.00
NORTH	116	$50.00
NORTH	117	$320.00
NORTH	118	$50.00
NORTH	119	$500.00
NORTH	120	$50.00
SOUTH	201	$50.00
SOUTH	202	$500.00
SOUTH	203	$500.00
SOUTH	204	$50.00
SOUTH	205	$500.00
SOUTH	206	$320.00
SOUTH	207	$50.00
SOUTH	208	$500.00
SOUTH	209	$50.00
SOUTH	210	$320.00

My goal is to get a table that looks like this

DIVISION	s^2
NORTH	$30,000.00
SOUTH	$45,360.00

I have a table-object in Qlik that has Division as a dimension and want to add a measure that will give me s^2 based on the formula above. I have not been able to create a Master Measure that will give me an accurate value for AVG(SALES_SUM) as an average of SALES_SUM within the DIVISION dimension

My current expression to be inserted into a table with one Dimension (DIVISION):

Sum(

Aggr(

POW(SALES_SUM - AVG(SALES_SUM),2)

,STAFF_ID)

)

(COUNT(DISTINCT CSN)-1)

Re: Sample Variance over a Dimension

sunny_talwar — Thu, 20 Apr 2017 16:31:50 GMT

May be try this

Sum(Aggr(POW((SALES_SUM - AVG(TOTAL <DIVISION> SALES_SUM),2), STAFF_ID, DIVISION))

(COUNT(DISTINCT CSN)-1)

Re: Sample Variance over a Dimension

Anonymous — Sun, 23 Apr 2017 19:02:53 GMT

Yes, I guess it should be aggregated over the division. What is 'CSN' and why do you have it distinct? If it is the staff_id, they are already unique. If they are not unique, you'll need to sum the sales_sum by staff_id as well. Otherwise you'll loose data.

Re: Sample Variance over a Dimension

swuehl — Sun, 23 Apr 2017 20:21:19 GMT

It could be enough to use the built in Qlik function Stdev():

=Sqr(Stdev( SALES_SUM))

used as expression in a chart with dimension DIVISION.

DIVISION	Sqr(Stdev(SALES_SUM))
	$35205,19
NORTH	$30000,00
SOUTH	$45360,00