Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Folks,
I'm using below expression in a straight table :
=if(Match(Name,'E'),NetWorkDays(min({$<Name=>}Date),max(Date))*8 ,NetWorkDays(min({<Name=>}Date),max({<Name=>}Date))*8)
Which is giving me desired output.
But when I'm using the same expression in bar chart results are different. For example :
When I'm selecting below Name ="A" , the correct number is 168 , but Bar chart is showing me 48
and same for E straight table showing me 48 , but bar 8.
Please suggest why , attached is sample data.
Note : For only Name ="E" Date is database is 9/8//2017 , so just for this calculation =NetWorkDays(min(Date),max(Date))*8) should run until 8th of Sep and for rest Names it should consider all Dates except weekends , doesn't matter if corresponding Date value available in DB and that's why I have created Master Calendar.
Regards,
Amit
Your requirement all makes sense, but how am I suppose to tell the application about when an employee has left? Is there a way to identify this date? May be an Exit date or something?
Hi Sunny,
Yes that's the issue , we don't have any hint or field which tells when an employee has left. But what I'm thinking is to create a variable vEmp and let the owner of this application add other's folks leaving organization into vEmp.
At the moment vEmp=E and the last date of this employee is the last available "Date" against number of "Sheets" entered. See below for E it is :
Regards,
AS
So as we move along in time and there are 5000 employees and 400 left... you are hoping that user will enter that in a variable? I think this is not a sound approach... do you want to may be get the end_date flagged in your database?
Anyways will try to get this working with your variable idea
So, based on variable, I am creating a EndDate for the employee which have ended employment....
Script
LET vMinDate = num(makedate('2017'));
LET vMaxDate = num(today());
Datefield:
LOAD
$(vMinDate) + IterNo() -1 as Datefield
AUTOGENERATE (1)
WHILE $(vMinDate) + IterNo() -1 <= $(vMaxDate);
Calendar:
LOAD
Date(Datefield,'M/D/YYYY') as %Date,
date(Datefield) as Date,
year(Datefield) as Year,
month(Datefield) as Month,
day(Datefield) as Day,
week(Datefield) as Week,
weekday(Datefield) as Weekday
RESIDENT Datefield;
Table:
LOAD *,
If(SubStringCount('$(vEndedEmployee)', Name), 1, 0) as EndedFlag;
LOAD * INLINE [
Name, %Date, Sheets
A, 9/1/2017, 3
A, 9/2/2017, 1
A, 9/3/2017, 1
A, 9/4/2017, 1
A, 9/5/2017, 1
A, 9/6/2017, 1
A, 9/7/2017, 1
A, 9/8/2017, 1
B, 9/1/2017, 2
B, 9/4/2017, 3
B, 9/6/2017, 6
B, 9/8/2017, 3
B, 9/11/2017, 7
B, 9/13/2017, 5
B, 9/18/2017, 1
B, 9/19/2017, 11
B, 9/20/2017, 8
B, 9/21/2017, 3
B, 9/25/2017, 2
B, 9/26/2017, 5
B, 9/27/2017, 3
B, 9/28/2017, 8
B, 9/30/2017, 2
C, 9/1/2017, 2
C, 9/4/2017, 5
C, 9/5/2017, 2
C, 9/6/2017, 3
C, 9/7/2017, 3
C, 9/8/2017, 1
C, 9/11/2017, 1
C, 9/12/2017, 4
C, 9/14/2017, 2
C, 9/18/2017, 5
C, 9/19/2017, 2
C, 9/20/2017, 7
C, 9/21/2017, 2
C, 9/25/2017, 2
C, 9/26/2017, 7
C, 9/27/2017, 5
C, 9/28/2017, 2
D, 9/12/2017, 1
D, 9/13/2017, 1
D, 9/19/2017, 2
D, 9/20/2017, 1
D, 9/26/2017, 1
D, 9/27/2017, 23
D, 9/28/2017, 1
E, 9/8/2017, 50
E, 6/1/2017, 1
E, 6/1/2017, 2
E, 6/5/2017, 1
E, 6/6/2017, 1
E, 6/6/2017, 4
E, 6/7/2017, 1
E, 6/8/2017, 1
E, 6/9/2017, 1
E, 6/13/2017, 1
E, 6/13/2017, 4
E, 6/14/2017, 1
E, 6/15/2017, 1
E, 6/16/2017, 1
E, 6/19/2017, 1
E, 6/20/2017, 3
E, 6/22/2017, 1
E, 6/22/2017, 2
E, 6/23/2017, 1
E, 6/27/2017, 3
E, 6/28/2017, 1
E, 6/29/2017, 1
A, 6/1/2017, 3
A, 6/2/2017, 2
A, 6/5/2017, 1
A, 6/6/2017, 1
A, 6/7/2017, 1
A, 6/8/2017, 1
A, 6/8/2017, 5
A, 6/12/2017, 1
A, 6/12/2017, 2
A, 6/13/2017, 1
A, 6/15/2017, 1
A, 6/16/2017, 1
A, 6/20/2017, 1
A, 6/20/2017, 2
A, 6/21/2017, 2
A, 6/22/2017, 2
A, 6/27/2017, 4
A, 6/27/2017, 5
A, 6/29/2017, 1
];
Left Join (Table)
LOAD Name,
Max(%Date) as EndDate
Resident Table
Where EndedFlag = 1
Group By Name;
Expression
NetWorkDays(Min(TOTAL {<Name=>}Date), RangeMin(Max(TOTAL {<Name=>}Date), Max({<Name=>}EndDate)))*8
Thank u so much Sunny for all ur help!
You are absolutely right that with bigger employee number , it doesn't make sense to enter names, but here this requirement is to help very small team of size 4-5 max , so some manipulations can be done as we did by variable.
Regards,
AS
Sounds good .. As long as the list of employee is small... the below should work.... the only thing I have not done is test the below code with 2 employees leaving... I think the only thing I see breaking is the variable's use in the table... but other than that... I think it should work...
Best,
Sunny
understood! Thanks!
Regards,AS
Sunny sorry one more question.
Can we also create StartFlag against Name for Min(%Date) as StartDate
If(SubStringCount('$(vStartededEmployee)', Name), 1, 0) as StartedFlag;
than how should expression will change:
NetWorkDays(Min(TOTAL {<Name=>}Date), RangeMin(Max(TOTAL {<Name=>}Date), Max({<Name=>}EndDate)))*8 ???
Thanks,
AS
I will have to play around with this a little... give me some time and I will get back to you
Thank u so much , but let me tell u why i nee this.
Suppose if I change this to start with 2014
Than with our logic it will start calculating hours per day for 2014 onward , but actually it should start for some employee from the min(%Date) they have started entering sheet numbers.
Regards,
AS