3 Replies Latest reply: Apr 30, 2017 8:13 PM by Marco Wedel RSS

    Correct sum dates!

    juan prado

      Hey guys!

       

      I need your help again! I have the following table and i need to sum the days but if you can see, the sum for the days returns me 309 but i need 307 because the two last rows have dates from and to included into the previous row (in bolds)

       

      How can i have the value in red.

       

       

      %IDfromtoto-from+1
      309
      3891205/07/201605/08/201632
      3891206/08/201606/09/201632
      3891207/09/201607/10/201631
      3891208/10/201608/11/201632
      3891209/11/201609/12/201631
      3891210/12/201623/12/201614
      3891224/12/201607/05/2017135
      3891208/03/201708/03/20171
      3891209/03/201709/03/20171
      307

       

       

      Thanks!

       

      marcowedel

      stalwar1

      franky_h79

      pcammaert

      kush141087

      vadim.grab

        • Re: Correct sum dates!
          kushal chawda

          I did not completely understand your below statement

          "two last rows have dates from and to included into the previous row"

          Can you please elaborate more?

          • Re: Correct sum dates!
            Marcus Sommer

            I think you need to flag or correct this within the script maybe with something like this:

             

            t0:

            load %ID, from, to from source;

             

            t1:

            load

                 %ID, from as from_old, to as to_old,

                 if(from < previous(to), previous(to) + 1, from) as from,

                 if(to < previous(to), from - 1, to) as to

            resident t0 order by %ID, from;

             

            t2:

            load *, to-from+1 as [to-from] resident t1;

             

            drop tables t0, t1;

             

            I'm not sure that this will be catch all possible cases and you might need to extend the logic to further checks within the if-loops or some more loadings which will flag and/or remove certain records if they aren't valid in your requirement.

             

            - Marcus

            • Re: Correct sum dates!
              Marco Wedel

              Hi,

               

              one possible solution might be:

               

              QlikCommunity_Thread_258721_Pic1.JPG

               

              table1:
              LOAD RecNo() as %Key,
                  %ID,
                  from,
                  to
              FROM [https://community.qlik.com/thread/258721] (html, codepage is 1252, embedded labels, table is @1, filters(Remove(Row, Pos(Top, 2)),Remove(Row, Pos(Top, 11))));
              
              tabDateLink:
              LOAD %Key,
                  Date(from+IterNo()-1) as Date
              Resident table1
              While from+IterNo()-1 <= to;
              
              tabCalendar: 
              LOAD *, 
                  Day(Date) as Day, 
                  WeekDay(Date) as WeekDay, 
                  Week(Date) as Week, 
                  WeekName(Date) as WeekName, 
                  Month(Date) as Month, 
                  MonthName(Date) as MonthName, 
                  Dual('Q'&Ceil(Month(Date)/3),Ceil(Month(Date)/3)) as Quarter, 
                  QuarterName(Date) as QuarterName, 
                  Year(Date) as Year, 
                  WeekYear(Date) as WeekYear;   
              LOAD Date(MinDate+IterNo()-1) as Date 
              While MinDate+IterNo()-1 <= MaxDate; 
              LOAD Min(Date) as MinDate, 
                  Max(Date) as MaxDate 
              Resident tabDateLink;
              

               

              see also:

              Creating Reference Dates for Intervals

               

              hope this helps

               

              regards

               

              Marco