Count data until the month of the dimension ... help?

[Anonymous]

I'm sure it is not so difficult, but I just can't find the solution ...

I've got some data over different years. What I want is to calculatie the totals per month per year.
But the data of the past years should also be taken into account, so all data should be counted until the month in the dimention.

So if I have some data like

1-2-2006
1-3-2006
1-6-2007
3-8-2008
12-6-2009
14-1-2010
6-2-2010
8-6-2010

resulting in something like:
2009 2010
jan 4 6
feb 4 7
mrt 4 7
apr 4 7
may 4 7
jun 5 8
jul 5
aug 5
sep 5
oct 5
nov 5
dec 5

Anyone got suggestions?

[johnw · Accepted solution]

I'd create a table linking the dates to all later months. Then just use AsOfMonth and AsOfYear as your dimensions, and count(Date) as your expression. Here's an example script. See attached for the results:

[Data]:
LOAD * INLINE [
Date
1-2-2006
1-3-2006
1-6-2007
3-8-2008
12-6-2009
14-1-2010
6-2-2010
8-6-2010
];
LEFT JOIN ([Data])
LOAD
month(Date) as AsOfMonth
,year(Date) as AsOfYear
,monthend(Date) as AsOfMonthYear
;
LOAD
addmonths(date#('1-1-2006','D-M-YYYY'),recno()) as Date
AUTOGENERATE 53
;
INNER JOIN ([Data])
LOAD *
RESIDENT [Data]
WHERE Date <= AsOfMonthYear
;

[Anonymous]

Working on my final solution ... but this will do the most part of it.

Thx again 😉