dynamic sum (sales)

[shayraber]

hello,

I have a fact table containg sales details (among other stuff...).

I'm tring to create a graph where my calendar's YearMonth is the dimension and the expression is sum([Sales Value]).

the trick is that i want each value of my dimension will NOT show the sum of sale's value of that YearMonth, BUT the sum of sale's value for the previous 6 month.

for example:

if my dimension is for the year 2013 it will contain 201301, 201302, 201303,...

and the value of sum([Sales Value]) will be for the period between 201207 to 201212 for 201301,

between 201208 to 201301 for 201302,

between 201209 to 201302 for 201303,

etc.

can someone please assist me?

[RSvebeck]

Hi!

I solved this some time ago using IntervallMatch.

Basically like this:

Sales:

LOAD  
Period,
Sales,
CustomerID,
ItemID
FROM sales.qvd (qvd);

IntervallTable:
LOAD
RowNo() as RowNumber,
year(AddMonths(today(),-RowNo()-11)) & '-' & num(Month(AddMonths(today(),-RowNo()-11)),'00')
& ' - ' & 
year(AddMonths(today(),-RowNo())) & '-' & num(Month(AddMonths(today(),-RowNo())),'00') AS Intervall,
year(AddMonths(today(),-RowNo()-11)) &  num(Month(AddMonths(today(),-RowNo()-11)),'00') AS [Period from],
year(AddMonths(today(),-RowNo())) &  num(Month(AddMonths(today(),-RowNo())),'00') AS [Period tom]
AUTOGENERATE 24;

INNER JOIN (Sales)

INTERVALMATCH (Period)
LOAD [Period from], [Period tom]
RESIDENT IntervallTable;

LEFT JOIN (Sales)
LOAD * RESIDENT IntervallTable;

DROP TABLE IntervallTable;

[RSvebeck]

And I should maybe add, in my example above, I create an Intervall-dimension "rolling 12 months" , so in your example it must be changed to Rolling 6 months.

[#4 · Accepted solution]

Hi Shay Raber,

see the attached example if it contains what you are looking for.

Good luck!

Rainer

[shayraber]

thanks

exactly what i was looking for