Expression results different in Straight and Bar chart

[amit_saini]

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

[sunny_talwar]

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?

[amit_saini]

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

[sunny_talwar]

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

[sunny_talwar · Accepted solution]

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

[amit_saini]

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

[sunny_talwar]

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

[amit_saini]

understood! Thanks!

Regards,AS

[amit_saini]

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

[sunny_talwar]

I will have to play around with this a little... give me some time and I will get back to you

[amit_saini]

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