Date within an interval.

[Anonymous]

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;

[ariel_klien]

Hi,

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

BR

Ariel

[Anonymous]

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

I do like your syntax though..

Robb

[#4]

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?

[#5]

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)

[Anonymous]

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:

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

Robb

[Anonymous]

Thanks vishwaranjan,

but this didn't work... you can run the same test using my above provided script. I think my problem is how I built the model... then your expression should work fine.

Robb

[#8]

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.

[er_mohit]

r u tried  with floor function this code

date(floor (start_date)) as DATE

hope it helps