Accumulated quantity for each date/month between a date range

[Anonymous]

Hi guys,

I'm trying to create a chart in Qlikview that gives the cumulative quantity for each date between a date range.

For eg. if the input excel data was :

Product	Quantity	Start Date	End Date
X	10	01/01/2017	03/15/2017
Y	5	02/06/2017	04/30/2017
Z	15	06/10/2017	08/03/2017

How can I create a bar chart from this data to look like this :

Any help would be much appreciated.

Thanks,

Ben

[antoniotiman]

Hi Bennet

Table:
LOAD Product,
Quantity,
[Start Date],
[End Date],
MonthName(AddMonths([Start Date],IterNo()-1)) as Month
FROM
"https://community.qlik.com/message/1285250"
(html, codepage is 1252, embedded labels, table is @1)
While Month(AddMonths([Start Date],IterNo()-1)) <= Month([End Date]);

LOAD Min([Start Date]) as MinDate,Max([End Date]) as MaxDate
Resident Table;
Let vMinDate = (Peek('MinDate'));
Let vMaxDate = (Peek('MaxDate'));
LOAD DISTINCT MonthName($(vMinDate)+RecNo()-1) as Month
AutoGenerate $(vMaxDate)-$(vMinDate)+1;

Regards,

Antonio

[Anonymous]

Hey Antonio,

That does work, but if the data set is large and the date range is a couple of years apart it takes ages to load.

Ben

[antoniotiman]

Another way (but it may not work with your data model)

Table:
LOAD Product,
Quantity,
[Start Date],
[End Date],
MonthName([Start Date]) as Month1,MonthName([End Date]) as Month2
FROM "https://community.qlik.com/message/1285250"
(html, codepage is 1252, embedded labels, table is @1);
LOAD Min([Start Date]) as MinDate,Max([End Date]) as MaxDate
Resident Table;
Let vMinDate = (Peek('MinDate'));
Let vMaxDate = (Peek('MaxDate'));
LOAD DISTINCT MonthName($(vMinDate)+RecNo()-1) as Month
AutoGenerate $(vMaxDate)-$(vMinDate)+1;

Dimension : Month

Expression : Sum(If(Month >= Month1 and Month <= Month2 ,Quantity))

[Anonymous]

That was clever but yea, I don't think it exactly fits my data model. Most of the time QV hangs when I try running the script.