Add Year/Month within date range(Qlikview)

[Tugge1]

Hi, I need some help!

I have a script that creates a table showing the static quantity of a product in stock during a "from date" to a "to date", it looks like this:

Product	FromDate	ToDate	Quantity
1	2021-06-08	2021-10-14	20
1	2021-10-15	2021-11-10	25
1	2021-11-11	2022-02-04	27
1	2022-02-05	2022-02-23	4

But I need the script to add rows with year/month that fit into the range of each "FromDate" to "ToDate", still with the same stock quantity. And i also need a new column "QD" that shows the total of days in each month the product have been in stock, see example below. Is this possible to do? 

Product	FromDate	ToDate	Quantity	YearMonth	QD
1	2021-06-08	2021-10-14	20	202106	23
1	2021-06-08	2021-10-14	20	202107	31
1	2021-06-08	2021-10-14	20	202108	31
1	2021-06-08	2021-10-14	20	202109	30
1	2021-06-08	2021-10-14	20	202110	14

 Best regards/ Patric

[brunobertels]

Hi 

Try this 

[Table]:
Load
Product,
Quantity,
FromDate,
ToDate,
MonthName(FromDate +IterNo()-1) as MonthYear
while FromDate + IterNo()-1<= ToDate;

Load
Product,
date(FromDate,'YYYY-MM-DD') as FromDate,
date(ToDate,'YYYY-MM-DD') as ToDate,
Quantity
;
LOAD * INLINE
[
Product,FromDate,ToDate,Quantity
1,2021-06-08,2021-10-14,20
1,2021-10-15,2021-11-10,25
1,2021-11-11,2022-02-04,27
1,2022-02-05,2022-02-23,4
](delimiter is ',');

You may get this kind of table 

Add this as mesure to calcualte number of days : 

if(MonthName(FromDate) = MonthYear ,monthend(FromDate)-FromDate,
if(MonthName(ToDate) = MonthYear ,day(ToDate),

day(monthend(MonthYear))))

[MarcoWedel]

maybe one solution could be:

table1:
LOAD *,
     Date(MonthStart(FromDate,IterNo()-1),'YYYYMM') as YearMonth,
     RangeMin(ToDate,DayStart(MonthEnd(FromDate,IterNo()-1)))-RangeMax(FromDate,MonthStart(FromDate,IterNo()-1))+1 as QD
Inline [
Product,FromDate,ToDate,Quantity
1,2021-06-08,2021-10-14,20
1,2021-10-15,2021-11-10,25
1,2021-11-11,2022-02-04,27
1,2022-02-05,2022-02-23,4
] While MonthStart(FromDate,IterNo()-1)<=ToDate;

hope this helps

Marco