topic Accumulated quantity for each date/month between a date range in QlikView

Accumulated quantity for each date/month between a date range

Anonymous — Sun, 11 Jun 2017 08:27:03 GMT

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

Re: Accumulated quantity for each date/month between a date range

antoniotiman — Sun, 11 Jun 2017 09:25:15 GMT

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

Re: Accumulated quantity for each date/month between a date range

Anonymous — Sun, 11 Jun 2017 16:20:32 GMT

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

Re: Accumulated quantity for each date/month between a date range

antoniotiman — Sun, 11 Jun 2017 17:28:31 GMT

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))

Re: Accumulated quantity for each date/month between a date range

Anonymous — Sun, 11 Jun 2017 18:03:17 GMT

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.