topic stdev function in QlikView

stdev function

Tue, 28 Dec 2010 10:27:33 GMT

Hello,

I'm trying to use the stdev function for a number of sells between two dates aggregating them by country and it doesn't work as I expected:

STDEV({<TIMESTAMP={">=$(=date(only(TIMESTAMP)-30)) <$(=only(TIMESTAMP))"}>}AGGR(COUNT({<TIMESTAMP={">=$(=date(only(TIMESTAMP)-30)) <$(=only(TIMESTAMP))"}>}DISTINCT SELL_ID),COUNTRY_ID))/30

I've tryed to calculate the average using the same expression and it works properly:

AVG({<TIMESTAMP={">=$(=date(only(TIMESTAMP)-30)) <$(=only(TIMESTAMP))"}>}AGGR(COUNT({<TIMESTAMP={">=$(=date(only(TIMESTAMP)-30)) <$(=only(TIMESTAMP))"}>}DISTINCT SELL_ID),COUNTRY_ID))/30

anyone have an idea of what i'm doing wrong?

Thank you !

stdev function

Tue, 28 Dec 2010 16:47:49 GMT

I know what i'm doing wrong but I don't know how to solve it,

the function for the average that I'm using now is:

AVG({<TIMESTAMP={">=$(=date(only(TIMESTAMP)-30)) <$(=only(TIMESTAMP))"}>}AGGR(COUNT({<TIMESTAMP={">=$(=date(only(TIMESTAMP)-30)) <$(=only(TIMESTAMP))"}>}DISTINCT SELL_ID),COUNTRY_ID,TIMESTAMP))

but I have a problem, it I doesn't count the null values and i need them,

thank you!

stdev function

johnw — Wed, 29 Dec 2010 02:04:51 GMT

What do you want to count null values AS? Zero? I'd replace them with zeros during the load, then. Basically, there's no such thing as the average of 10 and null, any more than there's an average of 10 and Sugar. Null is null, not zero. If you want zero, use zero.