Using Start Date and End Date data in Qlikview

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Using Start Date and End Date data in Qlikview

hic — Mon, 04 Apr 2016 06:32:20 GMT

You should use an Intervalmatch. See IntervalMatch

HIC

Re: Using Start Date and End Date data in Qlikview

Tue, 05 Apr 2016 02:21:45 GMT

Thanks Henric

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

Re: Using Start Date and End Date data in Qlikview

hic — Tue, 05 Apr 2016 09:32:05 GMT

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

topic Using Start Date and End Date data in Qlikview in QlikView

Using Start Date and End Date data in Qlikview

Re: Using Start Date and End Date data in Qlikview

Re: Using Start Date and End Date data in Qlikview

Re: Using Start Date and End Date data in Qlikview