Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
aboumejjane
Contributor

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_IDOrderIdYearMonthLastRequestDateMaxRequestDateMinRequestDate#days

#days

cumDays
101000341565420140130-4-201431-1-201411-1-20142121
101000341565420140230-4-201428-2-20141-2-20142849
101000341565420140330-4-201431-3-20141-3-20143180
101000341565420140430-4-201430-4-20141-4-201430110
101000341565420140530-4-201419-5-20141-5-201419

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

3 Replies
s_kunte23
Contributor III

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:

QlikCommunity_Thread_137785_Pic1.JPG.jpg

QlikCommunity_Thread_137785_Pic2.JPG.jpg

QlikCommunity_Thread_137785_Pic3.JPG.jpg

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:

QlikCommunity_Thread_137785_Pic4.JPG.jpg

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

Community Browser