topic Re: dynamic sum (sales) in QlikView

dynamic sum (sales)

shayraber — Tue, 30 Jul 2013 11:43:36 GMT

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?

Re: dynamic sum (sales)

RSvebeck — Tue, 30 Jul 2013 11:53:40 GMT

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;

Re: dynamic sum (sales)

RSvebeck — Tue, 30 Jul 2013 11:57:52 GMT

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.

Re: dynamic sum (sales)

Tue, 30 Jul 2013 12:15:12 GMT

Hi Shay Raber,

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

Good luck!

Rainer

Re: dynamic sum (sales)

shayraber — Tue, 30 Jul 2013 12:29:09 GMT

thanks

exactly what i was looking for