cummulating previous interval days per yearmonth

[Anonymous]

Hi Guys,

I would like to calculate the previous interval days per yearmonth. To do that I have a Maxdate and a mindate. For each orderId I get the amount of days. Now I want to add the previous days and add that to the next month. Please have a look at my example.

BA_ID	OrderId	YearMonth	LastRequestDate	MaxRequestDate	MinRequestDate	#days	#days	cumDays
1010003415	654	201401	30-4-2014	31-1-2014	11-1-2014		21	21
1010003415	654	201402	30-4-2014	28-2-2014	1-2-2014		28	49
1010003415	654	201403	30-4-2014	31-3-2014	1-3-2014		31	80
1010003415	654	201404	30-4-2014	30-4-2014	1-4-2014		30	110
1010003415	654	201405	30-4-2014	19-5-2014	1-5-2014		19

as you can see in the cumDays colum, the previous(#days) is added in the month 201402 (21+28). if the lastrequestdate colum is smaller than the maxrequesteddate, then null(), is returned.

How Can I achieve this in Qlikview

Thanks in advance

Sam

[Anonymous]

Hi,

see attached QVW. Let me know if this works for you!

Best regards

Stefan

[MarcoWedel]

Hi,

one possible solution could be:

tabOrder:
LOAD *,
    Date(MonthName(RequestDate),'YYYYMM') as YearMonth;
LOAD BA_ID,
    OrderDate,
    OrderId,
    MonthEnd(OrderDate,-1) as LastRequestDate,
    Date(RequestDate+IterNo()) as RequestDate
While RequestDate+IterNo()+1 <=OrderDate;
LOAD * INLINE [
    BA_ID, RequestDate, OrderDate, OrderId
    1010003415, 10-01-2014, 20-05-2014, 654
    2010003416, 17-01-2014, 02-04-2014, 321
];

Left Join (tabOrder)
LOAD *,
    MaxRequestDate-MinRequestDate+1 as #days;
LOAD BA_ID,
    OrderId,
    YearMonth,
    Date(Max(RequestDate)) as MaxRequestDate,
    Date(Min(RequestDate)) as MinRequestDate
Resident tabOrder
Group by BA_ID, OrderId, YearMonth;

hope this helps

regards

Marco

[MarcoWedel]

or generating the cumDays field at script level:

tabOrder:
LOAD *,
    Date(MonthName(RequestDate),'YYYYMM') as YearMonth;
LOAD BA_ID,
    OrderDate,
    OrderId,
    MonthEnd(OrderDate,-1) as LastRequestDate,
    Date(RequestDate+IterNo()) as RequestDate
While RequestDate+IterNo()+1 <=OrderDate;
LOAD * INLINE [
    BA_ID, RequestDate, OrderDate, OrderId
    1010003415, 10-01-2014, 20-05-2014, 654
    2010003416, 17-01-2014, 02-04-2014, 321
];

Left Join (tabOrder)
LOAD *,
    If(LastRequestDate>=MaxRequestDate,If(BA_ID=Previous(BA_ID),Peek(cumDays)+#days,#days)) as cumDays;
LOAD *,
    MaxRequestDate-MinRequestDate+1 as #days;
LOAD BA_ID,
    OrderId,
    YearMonth,
    LastRequestDate,
    Date(Max(RequestDate)) as MaxRequestDate,
    Date(Min(RequestDate)) as MinRequestDate
Resident tabOrder
Group by BA_ID, OrderId, YearMonth, LastRequestDate;

hope this helps also

regards

Marco