Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
hi henric
i have the count on a separate field ,,,here is my qvw file...
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)
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
thanks henric.. now I have to figure out how to do the autoload...
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
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
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!
I just saw that too. Neither of the two "faulty" resources have June 1st 2013 within their ranges.
HIC