4 Replies Latest reply: Aug 1, 2012 4:31 AM by bogd.cristescu RSS

    How to view time between two dates as an interval and compare with another interval?

      Hello,

       

      I am having trouble figuring out how to go about doing an analysis of a company's productivity. By selecting a specific time period, I want to be able to see how many agents are working and how many are free. When entering the data, agents have two fields , 'Date_debut'(as start date) and 'Date_fin' (as end date). While I was able to link a calendar to the document, when selecting a period between two dates it does not show me all the entries included in the interval between the two dates, but only if matches the them as single entries.

       

      I don't know if what I wrote makes any sense, bottom line is to be able to make QV see the time between the two dates as an inteval, compare the interval selected by me with the interval entered by the agents and be able to return the entries included in the interval.

       

      I just started working with QV so I would grately appreciate your patience as well as help, oppinions or thoughts on what to do with this situation.

       

      Thank you in advance,

       

      Bogdan

          • Re: How to view time between two dates as an interval and compare with another interval?

            Hello Ioannis,

             

            This is what I have used to generate the calendar in the document:

             

            //******************* Table Calender - Date_begin *******************
            Let varMinDate = Num(Makedate(2008,1,1));
            Let varMaxDate = Num(Makedate(Year(today())+1,12,31));
            Datefield:
            LOAD date($(varMinDate)+IterNo()-1) AS Datefield
            AUTOGENERATE (1) 
            WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);
            CalenderFinal:
            LOAD 
            Datefield AS date_begin,
            Year(Datefield) AS YearF,
            Month(Datefield) as MonthF,
            Dual(Date((Datefield),'MMMM'),Num(Month(Datefield))) as MonthsF,
            Floor(Monthstart(Datefield)) as MonthstarFt,
            Week(Datefield) AS WeekF,
            Weekday(Datefield) AS WeekdayF,
            Day(Datefield) AS DayF,
            'Q ' & Num(Ceil(Month(Datefield)/3),'(ROM)0') AS QuarterF, 
            Monthname(Datefield) AS RollMonthF,
            Num(Monthname(Datefield)) AS NumRollmontjF,
            (Num(Floor(Monthend(Datefield)))-Num(Floor(Monthstart(Datefield)))+1) as NumberDaysF,
            If(Num(Weekday(Datefield))<5,1,0) as WorkingDayF,
            YearToDate(Datefield,0) as YTDActualYearF,
            YearToDate(Datefield,-1) as YTD1PreYearF,
            YearToDate(Datefield,-2) as YTD2PreYearF,
            YearToDate(Datefield,1) as YTDFollowingYearF
            
            
            RESIDENT Datefield;
            //Delete temp table
            DROP TABLE Datefield;
            //Clean Up Variables
            SET varMinDate = ;
            SET varMaxDate = ;
            //*************************************End Script**********************************************
            
            

             

            This one is to generate the fields in which I select the beginning of the interval I want to see. Another one exactly like this is used to generate the fields for the end of the interval(Year, Month, Day).

             

            Is there anything specific I should look up in the document and post here?

             

            Thanks,

             

            Bogdan