Standard Deviation (2 from the average)

[BigRed]

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:
Load Distinct
Count([Field_I_Want_To_Count]) AS Field_I_Want_To_Count_a
Where A_Pct > A_Pct_a
;
Load Distinct
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!

[rwunderlich · Accepted solution]

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

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

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

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

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[BigRed]

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!

[BigRed]

@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)