I am having trouble getting my interval match to work correctly, or maybe it is and i'm misunderstanding its use.
My goal is that when my end user filters on a date or dates, my application will calculate the targets for the assigned position (per staff) within that timeframe.
Example: I am working as a electrician from 10/01//2016 to 12/31/2016 and my target during that time is 1000.
From 01/01/2017 to 03/31/2017 I am working as a lead electrician and my target is 1500.
Therefore when my end user selects 10/01/2016 to 03/31/2017 they see my target is 2500 total.
Right now I get a line or record for every start and end date for every position, when there should only be multiples if that employee has moved positions.
I have a a data structure like this.
and a load script as such
transfer_date-visitdate AS Notedays,
IF(transfer_date - visitdate<4,1,0) AS Compliant;
FROM [lib://It- Drive Reporting (tbh_erickd)/QVD Files\History.QVD]
(qvd) Where service <> 'Flex Funds';
IF(EmployeeStatus = 'A',1,0) AS EmployeeStatusFlag;
"Employee ID" AS staffid,
"Last Name First",
"Last Hire Date",
Today()-[Last Hire Date] AS Tenure,
UPPER([Supervisor Name]) As _Supervisor,
FROM [lib://It- Drive Reporting (tbh_erickd)/Qlik\Emp-Incentive-Link.xlsx]
(ooxml, embedded labels, table is [Emp-Incentive-Link]);
FROM [lib://It- Drive Reporting (tbh_erickd)/Target Table.xlsx]
(ooxml, embedded labels, table is Sheet1);
LIB CONNECT TO 'Microsoft_SQL_Server_tbh-warehouse (tbh_erickd)';
Date(StartDate) AS StartDate,
Date(StopDate) As StopDate,
IntervalMatch ( visitdate)
LOAD Min(visitdate) As MinDate
LET vMinDate = PEEK('MinDate',-1,'MinMaxDate')-1;
LET vMaxDate = FLOOR(MonthEnd(Today(1)));
Drop Table MinMaxDate;
Load Dual(fYear-1 &'/'& fYear, fYear) AS FYear,
Dual(Month, fMonth) AS FMonth,
DUAL ('Quarter' & Ceil(fMonth/3), Ceil(fMonth/3)) AS FQuarter,
Load Year + IF(Month>=$(vFM), 1,0) As fYear,
Mod(Month-$(vFM),12)+1 As fMonth,
DUAL('Quarter' & Ceil(Month/3), Ceil(Month/3)) AS Quarter,
Year(visitdate) As Year,
Month(visitdate) as Month,
Week(visitdate) as Week
LOAD Date(recno()+$(vMinDate)) As visitdate AutoGenerate vMaxDate - vMinDate;