# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for
Did you mean:
Not applicable

## Sample Variance over a Dimension

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

 SALES_SUM DIVISION STAFF_ID 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)

3 Replies
MVP

May be try this

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

/

(COUNT(DISTINCT CSN)-1)

Contributor III

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.

MVP

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