Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
May be try this
Sum(Aggr(POW((SALES_SUM - AVG(TOTAL <DIVISION> SALES_SUM),2), STAFF_ID, DIVISION))
/
(COUNT(DISTINCT CSN)-1)
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.
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 |