topic Re: Calculated column: count days between two dates on calendar table where "IsWorkDay" fi in App Development

Calculated column: count days between two dates on calendar table where "IsWorkDay" field = 1

Den — Sat, 16 Nov 2024 05:40:20 GMT

I have two tables, a CALENDAR table that has a custom calculation of IsWorkDay (I populate the IsWorkDay in the load script based on if the date occurs on a different table, so it doesn't follow a holiday or weekday schedule):

Date	IsWorkDay
1/1/2019	1
1/2/2019	0
1/3/2019	0
1/4/2019	1
1/5/2019	1

and an ORDER table:

Order	StartDate	EndDate	WorkDays (Expected Result)
1	1/1/2019	1/2/2019	0
2	1/1/2019	1/4/2019	1
3	1/1/2019	1/5/2019	2
4	1/2/2019	1/5/2019	1
5	1/2/2019	1/3/2019	0
6	1/5/2019	1/5/2019	0

If it matters, my tables in Qlik Sense are joined on Calendar.Date = Orders.StartDate.

I've done this in PowerPivot with a calculated column on my ORDER table using the following expression and got my intended results, I just can't figure out how to best handle it in Qlik Sense:

CALCULATE(
COUNTROWS(CALENDAR),
DATESBETWEEN('Calendar'[Date],ORDER[StartDate],ORDER[EndDate]),
'Calendar'[IsWorkDay]=1
) - 1)

I'd prefer to figure out how to handle this as a calculated column in Qlik Sense. Thanks in advance!

Re: Calculated column: count days between two dates on calendar table where "IsWorkDay" fi

Vegar — Tue, 04 Jun 2019 19:16:52 GMT

I find yor expected result hard to understand. Which day in the interval are you excluding, the first or the last? In Order 1 it seems like you are excluding the first (1/1/2019) but on order 4 you are excluding the last (1/5/2019).

If your expected values are correct then please explain for each row which workdays you are counting.

Re: Calculated column: count days between two dates on calendar table where "IsWorkDay" fi

Vegar — Tue, 04 Jun 2019 19:34:05 GMT

My previous question is still valid, but I did a solution assuming you want to disregard the first date in the interval as potential working day.

MAP_Workday: MAPPING LOAD * inline [ Date,IsWorkDay 1/1/2019,1 1/2/2019,0 1/3/2019,0 1/4/2019,1 1/5/2019,1]; Order: LOAD * Inline [ Order,StartDate,EndDate,WorkDays (Expected Result) 1,1/1/2019,1/2/2019,0 2,1/1/2019,1/4/2019,1 3,1/1/2019,1/5/2019,2 4,1/2/2019,1/5/2019,1 5,1/2/2019,1/3/2019,0 6,1/5/2019,1/5/2019,0]; left join LOAD sum(Workday) as Workdays, Order Group by Order; LOAD Order, applymap('MAP_Workday',StartDate + IterNo(),0) as Workday Resident Order while StartDate + IterNo() <= EndDate;

This gives me this table.

Re: Calculated column: count days between two dates on calendar table where "IsWorkDay" fi

Den — Wed, 05 Jun 2019 15:12:37 GMT

Oops, your statements are correct. The intended result was a mix-up on my part, it just needs to be the amount of business days between two dates minus 1 day. If the item was started and stopped on the same day, it would be zero days. If the item was started and stopped during a time period where there weren't any working days, then the calculation should give it a -1, which I will handle as zeroes in my KPI calculation.

I've been trying to implement your solution and I believe the issues I'm having is that my solution is using the GUI to synchronize tables after I import them to handle the relationships, so at the time of the load, there isn't a relationship made between my CALENDAR and ORDERS tables. It cannot determine a relationship in order to calculate this part of the script:

left join LOAD 
  sum(Workday) as  Workdays,
  Order

Re: Calculated column: count days between two dates on calendar table where "IsWorkDay" fi

Den — Wed, 05 Jun 2019 17:54:20 GMT

I got your script to work. Your calculations work perfectly and it is giving exactly my intended results (you were correct on how it should be calculating the amount of work days, it was my mix-up with putting in the incorrect expected result for the row of order 4). Thanks a ton, Vegar!

Re: Calculated column: count days between two dates on calendar table where "IsWorkDay" fi

Vegar — Fri, 07 Jun 2019 08:22:58 GMT

An alternative solution using interval match, it might be an faster approach than the mapping method. See attached qvf.

Script using intervalMatchResult in table using different total methods

Re: Calculated column: count days between two dates on calendar table where "IsWorkDay" fi

Den — Fri, 07 Jun 2019 14:53:23 GMT

I will try IntervalMatch to see if it is faster. As it turns out, I need to calculate my orders based on a column on the table, and then calculate the work days between the StartDate and EndDate based on two different IsWorkDay columns. So this is what my tables look like now:

Date	IsWorkDay_AreaA	IsWork_Day_AreaB
1/1/2019	1	1
1/2/2019	0	1
1/3/2019	0	0
1/4/2019	1	1
1/5/2019	1	1

ORDER:

OrderNo	OrderArea	StartDate	EndDate	WorkDays (Expected Result)
1	A	1/1/2019	1/2/2019	0
2	B	1/1/2019	1/2/1019	1
3	A	1/1/2019	1/4/2019	1
4	B	1/2/2019	1/4/2019	1
5	B	1/2/2019	1/5/2019	2

Is it possible to use this logic with an IntervalMatch? I tried implementing the original solution by mapping IsWorkDay_AreaA and filtering results on my orders where OrderArea='A' and it worked. When I tried to concatenate the orders by mapping IsWorkDay_AreaB and filtering results on my orders where OrderArea='B', it calculated the WorkDays for the orders in OrderAreaA, but the WorkDays for OrderAreaB were all null.