Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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;
Seems you forgot to give field name for networkdays calculation
NetWorkDays(Min(Date), Max(Date)) as workdays,
NetWorkDays(Min(Date), Max(Date), v$(Holidays)) as fieldname,
Thank you, but it seems like it's still not working even with the fieldname. Is there anything else you think might be useful?
if possible can you share error message and script what ur using...
NetWorkDays(Min(Date), Max(Date), v$(Holidays))
why v before $ in expression?
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
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;
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
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;