topic Re: IntervalMatch - count does not work when StartMonth = EndMonth but different years in QlikView

IntervalMatch - count does not work when StartMonth = EndMonth but different years

Wed, 12 Jun 2013 11:20:57 GMT

IntervalMatch: IntervalMatch (RepMonth) Load Start_Date_T1, End_Date_T1 Resident ResourceDetails; Hi I am a qlikview newbie... above is the code for my intervalmatch which I used to count employees based on start_date_t1 and end_date_T1... it works on all other scenarios except for the followign scenarios when start month/day = end month/day but different years example 06/01/2013 - 06/01/2014... what code should I use so that the range will include this scenario for counting employees... appreciate your response regards Lanie

Re: IntervalMatch - count does not work when StartMonth = EndMonth but different years

hic — Wed, 12 Jun 2013 11:33:07 GMT

The IntervalMatch prefix will create all combinations of the events (RepMonth) and the intervals, and this may mean that you get duplicates when an event belongs to several intervals. You should probably make the count on a field in another table.

See more on http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/04/intervalmatch

HIC

Re: IntervalMatch - count does not work when StartMonth = EndMonth but different years

Wed, 12 Jun 2013 11:51:06 GMT

hi henric

i have the count on a separate field ,,,here is my qvw file...

Re: IntervalMatch - count does not work when StartMonth = EndMonth but different years

Wed, 12 Jun 2013 11:55:31 GMT

can I add a conditional statement on my intervalmatch load statament so it will exclude the following scenario?

Year(Start_Date_T1)<>Year(End_Date_T1) and Month(Start_Date_T1)=Month(End_Date_T1) and Day(Start_Date_T1)=Day(End_Date_T1)

Re: IntervalMatch - count does not work when StartMonth = EndMonth but different years

hic — Wed, 12 Jun 2013 12:33:23 GMT

You could ... but that's not where the problem is.

The problem is that your RepMonth field contains garbage. It has not been correctly interpreted as dates. In the picture below, I have taken your field RepMonth and formatted it as an ISO date and as a number. There are dates there that you probably don't want.

You load these from an Excel file, but I think it would be better to generate them in a Load ... Autogenerate.

HIC

Re: IntervalMatch - count does not work when StartMonth = EndMonth but different years

Wed, 12 Jun 2013 12:50:17 GMT

thanks henric.. now I have to figure out how to do the autoload...

Re: IntervalMatch - count does not work when StartMonth = EndMonth but different years

hic — Wed, 12 Jun 2013 12:58:08 GMT

Try something similar to

Load *,

Date(RepMonth,'MMM-YYYY') as Month-Year,

Month(RepMonth) as Month;

Load *,

MakeDate(Year,monthNo) as RepMonth;

Load

2000 + RecNo() as Year,

IterNo() as monthNo

Autogenerate 10

While IterNo() <= 12;

HIC

Re: IntervalMatch - count does not work when StartMonth = EndMonth but different years

Wed, 12 Jun 2013 13:28:01 GMT

thanks again,, as attached is my updated doc.. however if I click June 2013 it still does not count the employees that fall under these dates

June 1, 2013 and June 1,2014

January 1, 2013 and January 1, 2014

my count expression on the Resource Count YTD should include both so it totals 360....

this is how it should look like...

but if I select RepMonth=June-2013...

my total looks like the following (which is wrong=> it should be 360 (Temp=118)

thanks in advance

Re: IntervalMatch - count does not work when StartMonth = EndMonth but different years

Wed, 12 Jun 2013 13:47:04 GMT

hi henric,

its working now.. apparently i have some wrong start dates.. which is supposed to be June 1, 2013 but is written as June 4, 2013...

thanks a lot!

Re: IntervalMatch - count does not work when StartMonth = EndMonth but different years

hic — Wed, 12 Jun 2013 13:49:14 GMT

I just saw that too. Neither of the two "faulty" resources have June 1st 2013 within their ranges.

HIC