Obtain effective working days using NetWorkDays()

[alespooletto]

I am trying to obtain the average of products made within the working days of the period from when I have available data until now, which means for each week, counting the total pieces made and divided by the number of days effectively worked, what was the average produced?

The problem is when I try to obtain the working days measure, as I get it like this currently within my Master Calendar:

MasterCalendar:
LOAD
    Date,
    Year(Date) as Year,
    'Q' & Ceil(Month(Date)/3) as Quarter,
    Month(Date) as Month,
    Week(Date,1,1) as Week,
    Day(Date) as Day,
    NetWorkDays(Min(Date), Max(Date), v$(Holidays)),
    WeekDay(Date) as Weekday,
    MonthName(Date) as MonthYear,
    Year(Date) & '-' & Week(Date,1,1) as WeekYear,
    Year(Date) & '-Q' & Ceil(Month(Date)/3) as QuarterYear 	
   
Resident ActivityLog;

In this case, I have a list of holidays that will be omitted. But even if I don't input anything there, I still get an error:

The following error occurred:

Invalid expression

The error occurred here:

MasterCalendar:

LOAD

Date,

Year(Date) as Year,

'Q' & Ceil(Month(Date)/3) as Quarter,

Month(Date) as Month,

Week(Date,1,1) as Week,

Day(Date) as Day,

NetWorkDays(Min(Date), Max(Date)),

WeekDay(Date) as Weekday,

MonthName(Date) as MonthYear,

Year(Date) & '-' & Week(Date,1,1) as WeekYear,

Year(Date) & '-Q' & Ceil(Month(Date)/3) as QuarterYear

Resident ActivityLog

---

The following error occurred:

Invalid expression

[anat]

Seems you forgot to give field name for networkdays calculation

NetWorkDays(Min(Date), Max(Date)) as workdays, 

[anat]

NetWorkDays(Min(Date), Max(Date), v$(Holidays)) as fieldname,

[alespooletto]

Thank you, but it seems like it's still not working even with the fieldname. Is there anything else you think might be useful?

[anat]

if possible can you share error message and script what ur using...

[anat]

NetWorkDays(Min(Date), Max(Date), v$(Holidays))

why v before $ in expression?

[alespooletto]

Yeah, my bad there it should be vHolidays, another variable that I created earlier. But as I mentioned, unfortunately this still doesn't work even without the Holidays. 

For the error, all I get is 'Invalid Expression'. You may find a screenshot attached here

[anat]

can you go through below script and check your script.

PayTable:

LOAD recno() as InvID, * INLINE [

InvRec|InvPaid

28/03/2012|28/04/2012

10/12/2012|01/01/2013

5/2/2013|5/3/2013

31/3/2013|01/5/2013

19/5/2013|12/6/2013

15/9/2013|6/10/2013

11/12/2013|12/01/2014

2/3/2014|2/4/2014

14/5/2014|14/6/2014

13/6/2014|14/7/2014

7/7/2014|14/8/2014

4/8/2014|4/9/2014

] (delimiter is '|');

NrDays:

Load *,

NetWorkDays(InvRec,InvPaid) As PaidDays

Resident PayTable;

Drop table PayTable;

[Fabiano_Martino_Intelco]

Hi @alespooletto 

I found a logic problem in your script.

You are using the same value for the star and the end date, so the difference will always be 1 day.

As @anat suggests, you should use two different values, without the Min and Max functions.

The original formula:

NetWorkDays(Min(Date), Max(Date), v$(Holidays)) as fieldname

should become:

NetWorkDays(Start_Date, End_Date, v$(Holidays)) as fieldname

[anat · Accepted solution]

you may try by creating 2 variables for min and max dates then use those dates in your networkdays logic.

PayTable:

LOAD recno() as InvID, * INLINE [

InvRec,InvPaid

28/03/2012,1

10/12/2012,2

5/2/2013,3

] (delimiter is ',');

NoConcatenate
min_max:
load min(InvRec) as min,max(InvRec) as max Resident PayTable;
vmin=Peek('min',-1,'min_max');
vmax=Peek('max',-1,'min_max');
drop Table min_max;

NrDays:

Load *,

NetWorkDays($(vmin),$(vmax)) As PaidDays // recomended
//NetWorkDays(min(InvRec),max(InvRec)) As PaidDays //getting error
Resident PayTable;

Drop table PayTable;