8 Replies Latest reply: Jun 11, 2013 2:59 PM by Mohit Sharma RSS

    Date within an interval.

    Robert Rosas

      I’m working on HR reporting. I’ve built a master calendar and linked the calendar DATE to start_date. I am trying to get a proper head count which is to count the number of employees that fall within the interval from report selection. The report should count employess that falls in between the interval start_date and end end_date given the selection DATE.

       

      My formula looks like this:

      [if DATE <= start_date and DATE >= end_date then count(EmpID)]

      *Note I put my formula in a text box.

       

      I get nothing.

       

      Any/all help/tips are appreciated.

       

      Here is my playpen script:

      ------------------------------------------------------------------------

      Let varMinDate=num(makedate( 2001, 1, 1 ));

      Let varMaxDate=num(makedate( 2013, 12, 31 ));

      Datefield:

      load date($(varMinDate)+ Iterno()-1) as Datefield

      autogenerate(1)

      while $(varMinDate)+ Iterno()-1 <= $(varMaxDate);


      Calendar:

      load distinct

      DayName(Datefield) as DATE,

      Year(Datefield) as Year,

      Month(Datefield) as Month,

      Day(Datefield) as Day
      resident Datefield;

      drop table Datefield;

      HR_Reporting:

      Load * INLINE

      [EmpID, start_date, end_date
      1, 3/15/2002, 9/20/2009
      2, 6/1/2005,
      3,10/1/2007,
      ]
      ;

      Concatenate(HR_Reporting)

      Load

      date(start_date) as start_date,

      date(end_date) as end_date,

      date (start_date) as DATE

      resident HR_Reporting;

        • Re: Date within an interval.
          Ariel Klien

          Hi,

           

          Try count({<DATE ={">=start_date<= end_date"}>}EmpID)

           

          BR

           

          Ariel

            • Re: Date within an interval.
              Robert Rosas

              Sorry Ariel... I get nothing... no joy.

               

              I do like your syntax though..

               

              Robb

                • Re: Date within an interval.

                  I believe that the problem here is linking start_date with DATE. It could work if Calendar were unlinked with HR_reporting table but if u have many data lines it would be very slow..

                   

                  Also, it could be the issue that some employees don't have end_date (you should make fake end_date like tommorow or smth)

                   

                  Btw, what is the reason of concatenating HR_reporting tables?

                    • Re: Date within an interval.
                      Robert Rosas

                      Thanks for you reply Sheshele,

                       

                      I need to link the 2 tables... If I don't link DATE to start_date then how should I do it?  I need a master calendar. Hence the need to concattenate the field... which now becasue of you thinking what I have done is wrong.

                       

                      If I look at my table I see that I have indeed duplicated my entry. I need to append the field DATE so that it matches the start_date.

                       

                      Here is what I did:

                      dateintervalquestionTables.JPG

                       

                      I'm not sure how to append my fields so that I have a link.

                       

                      Robb

                        • Re: Date within an interval.

                          I dont say that u dont need a master calendar. But let's answer the question what is the DATE? U have start_date, end_date and the third DATE which is neither the start_date nor the end_date.

                           

                          I have had a lot of projects where I have a lot of different dates but I need another one which is not related - we can talk about debts balance, premium earned for insurance, etc..

                           

                          Saying the truth I never solved the problem properly. Currently, I know three so-so ways. The one is to have a master calendar that is not linked to the data table but when it comes to 2m unique keys multiplied by 3 years, the solution is unfortunate. The other is to use variable as a DATE u are interested. Then u get ur answer for the specific date quickly and correctly but u loose the chance to see the dynamics. The last is to do that kind of caclulations in loading script. This is the best way but still if it comes when u multiply 2m unique keys by 3 years u get 600m lines and qlikview gets just too big and too slow.

                           

                          See attached qwv file. Sorry I changed dates to the format I usually use

                          Hope it helps.

                  • Re: Date within an interval.
                    Vishwaranjan Kumar

                    try this

                     

                    Count(if(DATE>=num(date(start_date) )and  DATE <= num(date(end_date)) , EmpID))

                     

                    or try this

                     

                     

                    count({<DATE ={">=$(=date(start_date))<=$(=date( end_date))"}>}EmpID)

                    • Re: Date within an interval.
                      Mohit Sharma

                      r u tried  with floor function this code

                      date(floor (start_date)) as DATE

                      hope it helps