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

[#1]

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

[giakoum]

some sample data would help

[#3]

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

[giakoum]

What you need are two dates as variables :

dateFrom and dateTo

Then some set analysis :

count (distinct {$<[Date_debut]={">=$(dateFrom)"}, [Date_fin] = {"<=$(dateTo)"}>} [agents])

the syntax is probably incorrect, as I do not know your field names, but you will get the point.

Hope this helps!

[#5]

Hello Ioannis,

Thank you for your reply and sorry for the late answer, I was out of the office. At this point I am stuck on a different project and I will need to put this on hold for a short while. I will try your solution and get back to you as soon as I have an answer.

Thank you for your help,

Bogdan