topic macro stdev in QlikView

macro stdev

Tue, 04 Jan 2011 10:15:38 GMT

Hi,

I need a macro to calculate the standard deviation,

Do you have it? the stdev function doesn't work correctly with my dataset

thanks!

macro stdev

tresB — Tue, 04 Jan 2011 11:28:57 GMT

Hi,

QV normally discourages the use of macro. if your calculation is different from stddev function, why can't we try it from the other standard functions available ? can you explain the logic of your calculation?

regards, tresesco

macro stdev

Tue, 04 Jan 2011 11:52:29 GMT

I need to calculate the stdev of the number of sales each day in a window of 30 days,

the problem is that i don't have data in all the days, if I use stdev function only uses the values I have but I need to count always 30 values,

if I don't have a value in a date I need a zero,

any idea?

macro stdev

tresB — Tue, 04 Jan 2011 12:34:21 GMT

Hi,

If we consider the standard deviation formula as a layman, then it is: SQRT(Sum((x-avg(x))^2) /N). Now you are having issue with the count part, i.e N (Total count of frequency). Let calculate this formula for your own. if N is fixed to 30, replace N with it or so. Note: here 'x' is your amount to be calculated.

Or, even you can go for putting 0 in the SalesAmount at the backend for those transaction when there is no transaction against some days.(possibly for that you have to maintain a common calendar).

hope this would help you.

regards, tresesco

macro stdev

Wed, 05 Jan 2011 09:30:17 GMT

Thank you but I can use that formula,

here is an example to explain it:

giving 3 values: 1, 1, 2, avg=1.3

if i use Sum((x-avg(x))^2) the result is (4-1.3)^2 instead of (1-1.3)^2+(1-1.3)^2+(2-1.3)^2,

(I know that in this case I could use stdev function but I don't tend to have that kind of cases)

i don't know if there is any way to use that formula propperly,

regards,

macro stdev

tresB — Wed, 05 Jan 2011 13:00:09 GMT

Hi,

use this formula :

SQRT(Sum((Value- avg(Total Value))*(Value-avg(Total Value)))/(N-1)) , Where N= total frequency. you can use N also or 30 according to your need. N-1 is used in STDEV function.

Have a look at the attachment. even i am happy to find the solution in practice. hope this would help you.

Sorry, there is some issues in my system, i am not being able to upload the app file now. try with the formula, it is enough itself i believe. if there is further issue, i wd try to upload next day.

regards, tresesco

macro stdev

Wed, 05 Jan 2011 16:02:26 GMT

Thank you Tresesco!

I've realised what i was doing wrong, I'm working with complex aggregations and I made a mistake, now it works perfecty

regards