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

14 Replies
Not applicable
Author

You are right thank you!

However, my reality looks a bit different so unortunately it did not work in my master document.

One of my formulas looks like the below:

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

Please see below result and attached example document.

Thanks in advance,

Olle

Capture1.JPG

Capture.JPG

swuehl
MVP
MVP

To see why this is happening, disable 'suppress zero values' on presentation tab.

You will see that there are actually a lot more records involved in calculating the stdev, many of them zero, which will lead to the large stdev value in QV.

You get the same number as in Excel by creating a Type list box and selecting 'A' (since all your selected date values should result in a Type 'A' aggregation, right?).

Thus your expression:

IF(Date<='01/01/2011',

SUM({<[TYPE]= {'A'}>}Amount),

SUM({<[TYPE]= {'B'}>}Amount))

does not filter the dates to only records belonging to Type 'A'. If you want this, I think you need to rework your expression.

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)

Not applicable
Author

Nice!

One question, what does the * do?

If it was not for the *, then it would give the same result as with the IF-statement or am I misunderstanding?

swuehl
MVP
MVP

No, the intersection operator * is used here to regard the user selections in Date field, if you remove the * operator (try it!), you will get the full Date range evaluated.

You can however simplify my above expression by using an additional Union operator:

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