topic Re: Obtain effective working days using NetWorkDays() in App Development

Obtain effective working days using NetWorkDays()

alespooletto — Fri, 19 Jan 2024 14:57:14 GMT

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

Re: Obtain effective working days using NetWorkDays()

anat — Fri, 19 Jan 2024 15:03:22 GMT

Seems you forgot to give field name for networkdays calculation

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

Re: Obtain effective working days using NetWorkDays()

anat — Fri, 19 Jan 2024 15:06:55 GMT

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

Re: Obtain effective working days using NetWorkDays()

alespooletto — Fri, 19 Jan 2024 15:54:20 GMT

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

Re: Obtain effective working days using NetWorkDays()

anat — Fri, 19 Jan 2024 16:00:03 GMT

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

Re: Obtain effective working days using NetWorkDays()

anat — Fri, 19 Jan 2024 16:04:51 GMT

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

why v before $ in expression?

Re: Obtain effective working days using NetWorkDays()

alespooletto — Fri, 19 Jan 2024 16:14:24 GMT

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

Re: Obtain effective working days using NetWorkDays()

anat — Fri, 19 Jan 2024 16:27:05 GMT

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;

Re: Obtain effective working days using NetWorkDays()

Fabiano_Martino_Intelco — Fri, 19 Jan 2024 16:51:58 GMT

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

Re: Obtain effective working days using NetWorkDays()

anat — Fri, 19 Jan 2024 16:59:44 GMT

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;

Re: Obtain effective working days using NetWorkDays()

Rohan — Sat, 20 Jan 2024 06:26:25 GMT

Hi @alespooletto ,

The error that you are getting in the script is because you are using aggr() functions without group by clause while using your NetworkDays() function.

Regards,

Rohan.