Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 !
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!
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.