Announcements
cancel
Showing results for
Did you mean:
Contributor II

## Standard Deviation (2 from the average)

Hello. In the Data Load Editor, I'd like to calculate 2 standard deviations from the mean/average. In SQL, this works.

SELECT
COUNT(1)
FROM TableName
WHERE A_Pct > (SELECT Avg(A_Pct)+2*Stdev(A_Pct) FROM TableName)

I found the following article but am not having success when trying to add similar logic in the Data Load Editor.
https://community.qlik.com/t5/QlikView-App-Dev/standard-deviation-rangestdev-for-my-values/m-p/40867...

I'd like to be able to do the following (i.e., count a field where A_Pct is greater than percent's that are 2 standard deviations from that. Again, the SQL above works just fine.)

Table1:
Count([Field_I_Want_To_Count]) AS Field_I_Want_To_Count_a
Where A_Pct > A_Pct_a
;
Field_I_Want_To_Count,
A_Pct, // This added b/c I need to reference that in the preceding load above.
Avg(A_Pct)+2*Stdev(A_Pct) as A_Pct_a    // I'd like for this to be 2 standard deviations from the mean/avg (i.e., A_Pct)
Resident Table2
Group By [Field_I_Want_To_Count], A_Pct
;

Hope this makes sense. Thank you!

Labels (2)

• ### Script

1 Solution

Accepted Solutions

I think you may have to do this in a two step load like:

TestData:
floor(NormInv(Rand(),0,1)*100) as F1
AutoGenerate 1000
;

Threshold:
Avg(F1) + (StDev(F1)* 2) as Threshold
Resident TestData
;
Let vThreshold = Peek('Threshold');

Counts:
Count(F1) as Counted
Resident TestData
Where F1 > \$(vThreshold)
;

3 Replies

I think you may have to do this in a two step load like:

TestData:
floor(NormInv(Rand(),0,1)*100) as F1
AutoGenerate 1000
;

Threshold:
Avg(F1) + (StDev(F1)* 2) as Threshold
Resident TestData
;
Let vThreshold = Peek('Threshold');

Counts:
Count(F1) as Counted
Resident TestData
Where F1 > \$(vThreshold)
;

Contributor II
Author

Thank you @rwunderlich, I was able to work that into my code and am getting results. I'll just need to vet those, but believe we're in good shape. Thank you so much!

Contributor II
Author

@rwunderlich, the following is what I'm trying to recreate:

Avg(A_Pct)+2*Stdev(A_Pct) as A_Pct_a

Rearranging your suggestion slightly will work. Thanks again! Note, you may have based your suggestion on my wording ("I'd like for this to be 2 standard deviations from the mean/avg") rather than how the SQL was written.

Avg(F1)+2*StDev(F1)