Hi,

Currently I'm developing a MDX query which should return the results
(average/sum) of the previous selected 30 days (rolling/moving).

However the problem is that my result set provides me with the days
available (with data) of a particular month.

I.e. October has 31 days, however the result returned from the OLAP
misses October 3, October 19, etc.... I got not 31 cells, but i.e. 27
cells.

I would still like to have the average of this month (meaning the usage
of 31 days). How can I detect the "missing" cells... and how do I know
what "Days" are missing?

Any help much appreciated,

Thanks,
Afd



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

do you generate your dimension using a table which contain ALL the days of
the year?
or you generate only the days available in your fact table?
if you are in the second case, then switch to the first one to make sutre
your dimension always copntain everyday

after, don't use "nonemptycrossjoin" syntax.

do you want a average which is:
Sum of the last 30 days / 30?
(so empty values = 0)

This formula count the number of days without sales in the last 30 days:
Count(filter(LastPeriods(30, closingperiod(Time.Days, Time.Currentmember)),
isempty(measures.sales)))

This formula return the average sales for the last 30 days (excluding empty
values)
Avg(LastPeriods(30, closingperiod(Time.Days, Time.Currentmember)),
measures.sales)

This formula return the average sales for the last 30 days (including empty
values)
Avg(LastPeriods(30, closingperiod(Time.Days, Time.Currentmember)),
CoalesceEmpty(measures.sales,0))

I hope this can help you.

"Aldo Deijkers" <afdeijkers (AT) aol (DOT) com> a écrit dans le message de news:
%23bky5nQ6EHA.3236 (AT) TK2MSFTNGP15 (DOT) phx.gbl...