Count of Working Days From Today

[derekjones]

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

[Gysbert_Wassenaar]

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.

[CELAMBARASAN]

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)

[derekjones]

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

[derekjones]

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

[Yogi]

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