topic Count of Working Days From Today in QlikView

Count of Working Days From Today

derekjones — Thu, 03 Jan 2013 18:21:23 GMT

Hi All

Need some guidance, I want to add to my script which builds the calendar table a field which lets me know for a specific date how many 'working' days have gone so far.

Example, below I have Date, I also have if the day is a Working Day e.g. even though 01/01/2013 was a Tuesday. What I want to derive is a count of how many working days have been since that date. So today we are on the 3rd Jan, so Working Days From Today is 0, Yesterday (2nd Jan), was a working day too so is 1 day behind and hence 1, 1st Jan was nota working day, so count stays at 1, 31st Dec was so count goes up to 2 etc.

Tomorrow, all the counts go up by one as today becomes 4th Jan, so 3rd no longer is 0, it's 1 etc.

FYI, the reason I need this is so I can use this to work out for example the last 20 days sales etc.

Date	Working Day	Working Days From Today
27/12/2012	Y	4
28/12/2012	Y	3
29/12/2012	N	2
30/12/2012	N	2
31/12/2012	Y	2
01/01/2013	N	1
02/01/2013	Y	1
03/01/2013	Y	0
04/01/2013	Y	0
05/01/2013	Y	0

I've added a simple Qlikview app with the above calendar table if it helps to use this to demonstrate how it can be done.

My thanks in advance for any help

Derek

Re: Count of Working Days From Today

Gysbert_Wassenaar — Thu, 03 Jan 2013 19:11:23 GMT

I'd use the networkingdays function and use a table (or variable) for the holidays to feed to the networkingdays function. See attached qvw. One solution is to use the script to calculate the working days. The possible disadvantage is you need to reload daily. If you do that anyway, then no problem. The other solution is to calculate the working days in the chart itself.

Re: Count of Working Days From Today

CELAMBARASAN — Thu, 03 Jan 2013 20:40:47 GMT

You can use this expression in chart

=NumMax(NetWorkDays(Date#(Date,'DD/MM/YYYY'), Today(),$(=Chr(39) &Concat({<WorkingDay={'N'}>} Date,Chr(39)&','&Chr(39)) &Chr(39)))-1,0)

Re: Count of Working Days From Today

derekjones — Fri, 04 Jan 2013 09:02:48 GMT

Thanks Gysbert

Defintely a workable solution. I don't actually have a list of the holidays (I basically have a calendar file with the days flagged as working days or not, including weekends) I can't see it being too difficult creating a list of all the non weekend holidays to achieve this. I will have a go at this today.

Yes, I reload daily, so my intention is to include this 'WorkingDaysFromToday' field in my calendar table to reduce the amount of calculation required in the application as this is to be used on reports using thousands of sales records to derive the data.

Thanks again for your suggestion.

Derek

Re: Count of Working Days From Today

derekjones — Fri, 04 Jan 2013 09:15:19 GMT

Hi Celambarasan

Thanks, the expression works a treat without the need of storing the nonweekend holidays in a variable.

I am however concerned the concat looks like it could be quite processing intensive over larger datasets.

My actual live application has a 4 year calendar and over 4 million sales records that have a date linked to the calendar, I will test this to see if there will be any performance issues in using this expression in my app. If so, I'll use Gysbert's suggestion above which will incorporate the calculation inside the calendar table.

Thanks again for your elegant solution.

Derek

Re: Count of Working Days From Today

Yogi — Wed, 25 Sep 2019 11:39:37 GMT

We can try this simple script as well:

Remaining working days in Month:

NetWorkDays(Date(today(), 'MM/DD/YYYY'),MonthEnd(Date(today(), 'MM/DD/YYYY'))) as RemainingMonthWorkingDays