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

    cummulating  previous interval days per yearmonth

    Aissam Boumejjane

      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

        • Re: cummulating  previous interval days per yearmonth
          Stefan Kunte

          Hi,

           

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

           

          Best regards

          Stefan

          • Re: cummulating  previous interval days per yearmonth
            Marco Wedel

            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
                Marco Wedel

                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