Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead 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

DIVISIONSTAFF_IDSALES_SUM
NORTH100$50.00
NORTH110$350.00
NORTH111$350.00
NORTH112$350.00
NORTH113$350.00
NORTH114$320.00
NORTH115$500.00
NORTH116$50.00
NORTH117$320.00
NORTH118$50.00
NORTH119$500.00
NORTH120$50.00
SOUTH201$50.00
SOUTH202$500.00
SOUTH203$500.00
SOUTH204$50.00
SOUTH205$500.00
SOUTH206$320.00
SOUTH207$50.00
SOUTH208$500.00
SOUTH209$50.00
SOUTH210$320.00

My goal is to get a table that looks like this

  

DIVISIONs^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
sunny_talwar

May be try this

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

/

(COUNT(DISTINCT CSN)-1)

Anonymous
Not applicable
Author

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.

swuehl
MVP
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