topic Re: StDev of sum in QlikView

StDev of sum

Wed, 24 Jun 2015 07:57:39 GMT

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

Re: StDev of sum

alexandros17 — Wed, 24 Jun 2015 08:12:49 GMT

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

Re: StDev of sum

Wed, 24 Jun 2015 09:06:26 GMT

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

Could you pls give me an example?

Re: StDev of sum

sgrice — Wed, 24 Jun 2015 09:11:48 GMT

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

Re: StDev of sum

jonathandienst — Wed, 24 Jun 2015 09:14:34 GMT

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

Re: StDev of sum

sgrice — Wed, 24 Jun 2015 09:15:16 GMT

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

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

Re: StDev of sum

Wed, 24 Jun 2015 09:41:57 GMT

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.

Re: StDev of sum

sgrice — Wed, 24 Jun 2015 10:09:48 GMT

I take it that qlikview is doing

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

Re: StDev of sum

Wed, 24 Jun 2015 10:14:37 GMT

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

Mystery of the day

Re: StDev of sum

swuehl — Wed, 24 Jun 2015 10:15:31 GMT

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

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

N being your sample size (count of values).

Re: StDev of sum

Wed, 24 Jun 2015 12:41:56 GMT

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

Re: StDev of sum

swuehl — Wed, 24 Jun 2015 13:22:54 GMT

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.

Re: StDev of sum

swuehl — Wed, 24 Jun 2015 13:31:03 GMT

Try a variable vUB definition like this:

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

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

Re: StDev of sum

Wed, 24 Jun 2015 14:07:09 GMT

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?

Re: StDev of sum

swuehl — Wed, 24 Jun 2015 14:12:09 GMT

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)