i think the logic is always the same also with n dimensions
- you must have all the months for all the dims
- when some dim or the year chenge restart
I add a dimension to my test data, now the dims are country and product
SET DateFormat='DD/MM/YYYY';
// start test data
x:
load
addmonths(MakeDate(2014),rowno()-1) as ym, // year month
2*rowno()*rowno()+rowno() as ytd // ytd value
AutoGenerate 24;
// just to test, add 2 dimensions
left join load * inline [
product
a
b
c
];
left join load * inline [
country
italy
germany
france
];
// end test data
// load ordered by .... and when one dimension or year change reset
// calc diff between row and previuos row ytd value
y:
NoConcatenate load
product, country,
ym,
ytd,
if(product<>Peek(product) or country<>peek(country) or year(ym)<>year(peek(ym)), ytd, ytd-Peek(ytd)) as mtd
Resident x
order by product, country, ym;
DROP Table x;
