How to calculate TargetDate as StartDate + ExtraDays when IsWorkDay=1.

[#1]

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Components.UserFiles/00.00.02.89.41/Calculate-dd_2D00_mm_2D00_yyyy-Plus-X-days-_2D00_-2010103002.qvw]

Hi guys,

I Need help to work around the present situation (please see the attachment):

I need to calculate Target Date as StartDate + ExtraDays when IsWorkDay=1.

In example:

If StartDate = 30/09/2010 (see Line No 15)

So, Target Date should be 04/10/2010

LineNo	StartDate	Day	IsWorkDay	ExtraDays
15	30/09/2010	Thu	1	3
16	01/10/2010	Fri	0
17	02/10/2010	Sat	0
18	03/10/2010	Sun	1
19	04/10/2010	Mon	1
20	05/10/2010	Tue	0
21	06/10/2010	Wed	1

Thanks in advance,

Aldo.

[johnw]

I think this:

date(rangesum(StartDate,if(IsWorkDay,ExtraDays))) as TargetDate

[#3]

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Components.UserFiles/00.00.02.89.41/Calculate-dd_2D00_mm_2D00_yyyy-Plus-X-days-_2D00_-2010103003.qvw]

Hi John,

Thanks for your answer.

Almost there, but having some errors, please take a look:

LineNo	StartDate	Day	IsWorkDay	ExtraDays	TargetDate
15	30/09/2010	Thu	1	3	03/10/2010	Should be 04/10/2010	Error = 1
16	01/10/2010	Fri	0		01/10/2010
17	02/10/2010	Sat	0		02/10/2010
18	03/10/2010	Sun	1		03/10/2010
19	04/10/2010	Mon	1		04/10/2010
22	07/10/2010	Thu	1	5	12/10/2010	Should be 14/10/2010	Error = 2
23	08/10/2010	Fri	0		08/10/2010
24	09/10/2010	Sat	0		09/10/2010
25	10/10/2010	Sun	1		10/10/2010
26	11/10/2010	Mon	0		11/10/2010	This is a Holiday…
27	12/10/2010	Tue	1		12/10/2010
28	13/10/2010	Wed	1		13/10/2010
29	14/10/2010	Thu	1		14/10/2010
35	20/10/2010	Wed	1	1	21/10/2010	Ok!
36	21/10/2010	Thu	1		21/10/2010
39	24/10/2010	Sun	1	7	31/10/2010	Should be 01/11/2010	Error = 1
40	25/10/2010	Mon	1		25/10/2010
41	26/10/2010	Tue	1		26/10/2010
42	27/10/2010	Wed	1		27/10/2010
43	28/10/2010	Thu	1		28/10/2010
44	29/10/2010	Fri	0		29/10/2010
45	30/10/2010	Sat	0		30/10/2010
46	31/10/2010	Sun	1		31/10/2010
47	01/11/2010	Mon	1		01/11/2010

[johnw]

I guess I'm confused. Why is 30/09/2010 + 3 ExtraDays = 04/10/2010? That's 4 days later, not 3 days later. Where does the extra day come from?

Does it have something to do with only adding work days, not just days? No, because Neither 01/10/2010 nor 02/10/2010 are work days, so if I added 3 work days, I'd be at 05/10/2010.

So, no idea what you're doing, then.

[#5]

Hi John,

Yeap, talking about neto working days, so if...

I start on 30/09/2010 at 08:00 this is my first working day

03/10/2010 will be the second working day,

04/10/2010 will be the third working day

and Target Date would be 05/10/2010.

Anyway, rangesum is not doing anything... ypu can see that Target Date is always StartDate + ExtraDays...

[johnw · Accepted solution]

Aldo wrote:Anyway, rangesum is not doing anything... ypu can see that Target Date is always StartDate + ExtraDays...

Rangesum() is just addition, but treats nulls as 0, while regular addition turns the entire thing null. So rangesum() wasn't intended to skip non-working days. I didn't realize that's what you wanted.

The below does what I think you want, counting ONLY the days you've identified as work days. In practice, you'd want to combine it with earlier loads, but it's simpler to show you something that could be done at the end of your script.

WorkDates:
LOAD DISTINCT StartDate as WorkDate
RESIDENT BaseDates
WHERE IsWorkDay
ORDER BY StartDate
;
LEFT JOIN (BaseDates)
LOAD
LineNo
,if(ExtraDays,date(fieldvalue('WorkDate',fieldindex('WorkDate',StartDate)+ExtraDays)),StartDate) as TargetDate2
RESIDENT BaseDates
;
DROP TABLE WorkDates
;

[#7]

That's exactly what I need!

Thanks a lot John!

Aldo.

[johnw]

I see one possible problem. Do you have any rows where you specify ExtraDays, but the StartDate is NOT a work day? If so, the fieldindex() would fail. But I don't know if this actually matters in your real data.

[#9]

Yes, you are right, but I am taking into consideration that StartDate must be a Working day.

Anyway,is there any work around to solve the problem? There is always some intelligence one that does exactly the opposite he should do...

[johnw]

Well, rather than fieldindex(), we could manually set a working date sequence to get that number, and set it on non working days without incrementing it. Again, you can probably combine some of this with earlier loads, but tacking it on the end, you can do this:

WorkDates:
LOAD DISTINCT StartDate as WorkDate
RESIDENT BaseDates
WHERE IsWorkDay
ORDER BY StartDate
;
LEFT JOIN (BaseDates)
LOAD
LineNo
,WorkDateSequence
,if(ExtraDays,date(fieldvalue('WorkDate',WorkDateSequence+ExtraDays)),StartDate) as TargetDate2
;
LOAD
LineNo
,StartDate
,ExtraDays
,rangesum(peek(WorkDateSequence),IsWorkDay) as WorkDateSequence
RESIDENT BaseDates
ORDER BY StartDate
;
DROP TABLE WorkDates
;