1 Reply Latest reply: Jul 1, 2015 6:18 AM by Kashif Feroz RSS

    Count between dates

    Kashif Feroz

      Hello everyone.

       

      I want to count members whose start date is before 15th of every month and end date is after 15th of every month.

       

      Please refer to the excel file attached. In sheet 1 is the main data. I want to count 'unique id'. In sheet 2, i've done it using counitfs function. i want to replicate the same in qlikview.

       

      Thanks

        • Re: Count between dates
          Sunny Talwar

          This may not be the best approach, but I get you the output you are looking for using IntervalMatch:

           

          Capture.PNG

           

          Script:

          Table:

          LOAD [Sr. No.],

              [unique id],

              [start date],

              [end date]

          FROM

          Community_170762.xlsx

          (ooxml, embedded labels, table is Sheet1);

           

           

          Temp:

          LOAD Min([start date]) as minDate,

            Max([end date]) as maxDate

          Resident Table;

           

           

          LET vMinDate = Num(Peek('minDate', 0, 'Temp'));

          LET vMaxDate = Num(Peek('maxDate', 0, 'Temp'));

          DROP Table Temp;

           

           

          Calendar:

          LOAD Date($(vMinDate) + IterNo() - 1) as Date

          AutoGenerate

            1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate);

           

          IntervalMatch:

          IntervalMatch(Date)

          LOAD Distinct [start date], [end date]

          Resident Table;


          Straight Table:

          Dimension: =If(Day(Date) = 15, Date)

          Expression: =Count([unique id])

           

          Attaching the qvw for your reference

           

          HTH

           

          Best,

          Sunny