3 Replies Latest reply: Oct 14, 2014 6:21 PM by Marco Wedel

# cummulating  previous interval days per yearmonth

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

• ###### Re: cummulating  previous interval days per yearmonth

Hi,

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

Best regards

Stefan

• ###### Re: cummulating  previous interval days per yearmonth

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

• ###### Re: Re: cummulating  previous interval days per yearmonth

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