Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
see attached QVW. Let me know if this works for you!
Best regards
Stefan
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
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