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

[#1]

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

[hic]

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

[#3]

hi henric

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

[#4]

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)

[hic]

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

[#6]

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

[hic]

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

[#8]

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

[#9]

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!

[hic]

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

HIC