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

StDev of sum

I have this Variable vUB (works great) which concists of the below expression: 

vUB =

({<[FLOW TYPE]= {'Actual EOD (OLD DATA)'}>}AMOUNT)
-
SUM({<[FLOW TYPE]= {'TCM  (OLD DATA)'}>}AMOUNT)
-
SUM({<[FLOW TYPE]= {'FW  (OLD DATA)'}>}AMOUNT)
-
SUM({<[FLOW TYPE]= {'O (Active) (OLD DATA)'}>}AMOUNT)
-
SUM({<[FLOW TYPE]= {'T (OLD DATA)'}>}AMOUNT)
-
SUM({<[FLOW TYPE]= {'S (OLD DATA)'}>}AMOUNT)
-
SUM({<[FLOW TYPE]= {'W → (OLD DATA)'}>}AMOUNT)
-
SUM({<[FLOW TYPE]= {'1W (OLD DATA)'}>}AMOUNT)

However when i try to calculate the StDev results as per below:

stdev(vUB)  = null

stdev($(vUB)) = error

any ideas?

Rgds,

Olle

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Try a variable vUB definition like this:

SUM({<[TYPE]= {'A'}, Date *= {"<=01/01/2011"} >}Amount) +

SUM({<[TYPE]= {'B'}, Date *= {">01/01/2011"} >}Amount)

View solution in original post

14 Replies
alexandros17
Partner - Champion III
Partner - Champion III

I think that the expression doesn't work because Sum and StDev are both aggregation functions, so you have to use stdev as you use sum, not togheter

Not applicable
Author

I see, so I have to use aggr some way in my expressions?

Could you pls give me an example?

sgrice
Partner - Creator II
Partner - Creator II

Use

stdev(ValueList(1,2,3,4))

but instead of 1,2,3,4

can be your Variable but you would need iit to be SUM(...),SUM(...) etc

jonathandienst
Partner - Champion III
Partner - Champion III

Assuming vUB works on its own (ie as expression in the chart =$(vUB) ), then you should be able use an Aggr() like this:

=stdev(Aggr(($(vUB)), dim1, ...  )

where dim1, ... is a comma separated list of all the chart/table dimensions

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
sgrice
Partner - Creator II
Partner - Creator II

Or if you just wanting STdev of all [FLOW TYPE]

stdev(aggr(sum(AMOUNT), [FLOW TYPE]))

Not applicable
Author

Closing in i think!

However getting different values when calculating in Excel and QV.

Any idea why? Pls see below, and pls see attached example doc.


Capture1.JPG

Capture.JPG

sgrice
Partner - Creator II
Partner - Creator II

I take it that qlikview is doing

STdev(Aggr(SUM({<TYPE={'A'}>}Amount)+SUM({<TYPE={'B'}>}Amount),Date))

?

Not applicable
Author

I beleive you are ríght, question is why it differs from the Excel result?

Mystery of the day

swuehl
MVP
MVP

Because you are using STDEV.P in Excel.

I believe the QV stdev() function works like STDEV.S in Excel.

For large sample sizes, both Excel functions (STDEV.S and STDEV.P) converge.

You can transform Stdev.S into Stdev.P by calculating something like

Stdev.S * sqrt(N-1) / sqrt(N)  = Stdev.P

or

Stdev.S = sqrt(N) /sqrt(N-1) * Stdev.P

N being your sample size (count of values).