Using Start Date and End Date data in Qlikview

[#1]

Hi all,

I would like to seek help on how to use data with Start Date and End Date in QlikView.

I have 3 staff: A, B, and C. A and B are seasoned staff, so their target is fixed for the whole month of April at 140, hence start date is 1st April and End Date is 30th April. However, C is new, and has a weekly target to follow as per the start and end dates for respective weeks.

Staff Name	Start Date	End Date	Target
A	01/04/2016	30/04/2016	140
B	01/04/2016	30/04/2016	140
C	01/04/2016	07/04/2016	120
C	08/04/2016	14/04/2016	125
C	15/04/2016	21/04/2016	130
C	22/04/2016	28/04/2016	135
C	29/04/2016	30/04/2016	140

QlikView captures their actual performance every day that they are working, example as per below. Is there any way i can feed the above target data into Qlikview to tag the respective targets to their daily performance? Ultimately, i want to calculate their % performance by taking the actual performance / target, in percentage, only for days that they are working and have actual performance stats.

The main problem i have is with C's moving weekly target. If i feed the above target into Qlikview, common linkage will be Staff Name.

Date	Staff Name	Actual performance
01/04/2016	A	150
01/04/2016	B	160
02/04/2016	A	151
02/04/2016	C	171
03/04/2016	B	162
03/04/2016	C	172
04/04/2016	A	153
04/04/2016	B	163
05/04/2016	A	154
05/04/2016	C	174
06/04/2016	B	165
06/04/2016	C	175
07/04/2016	A	151
07/04/2016	B	161
07/04/2016	C	171
08/04/2016	A	150
08/04/2016	B	160
09/04/2016	A	150
09/04/2016	C	170
10/04/2016	B	160
10/04/2016	C	170
11/04/2016	A	150
11/04/2016	B	160
12/04/2016	A	150
12/04/2016	C	170
13/04/2016	B	160
13/04/2016	C	170
14/04/2016	A	150
14/04/2016	B	160
14/04/2016	C	170

Your advice would be greatly appreciated. Thank You.

[hic · Accepted solution]

You should use an Intervalmatch. See IntervalMatch

HIC

[#3]

Thanks Henric

I did encounter a popup saying i had a loop, but data set seems to be working for now.

[hic]

By the way - since you have [Staff Name] in both tables, you probably need a slightly more elaborate script to remove a circular reference. See Slowly Changing Dimensions and IntervalMatch and Slowly Changing Dimensions.

HIC