- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
dynamic sum (sales)
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?
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
thanks
exactly what i was looking for