25 Replies Latest reply: Feb 2, 2018 7:22 AM by Sunny Talwar RSS

    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

        • Re: Expression results different in Straight and Bar chart
          Amit Saini

          In general it should show 8 hours a day except weekends , doesn't matter if last day (Entry) against Sheets is 9/8/2017 , it should calculate 8*per day till month end i.e. 9/29/2017

           

           

          And for only Name =E , it should calculate perday*8 till 9/8/2017 (The last Date entered against "Sheets") as it was last day for E in firm.

           

           

           

          Regards,

          AS

          • Re: Expression results different in Straight and Bar chart
            Sunny Talwar

            Do you need this?

             

            =if(Match(Only({$<Name=>}Name),'E'),NetWorkDays(min(TOTAL {$<Name=>}Date),max(TOTAL Date))*8 ,NetWorkDays(min(TOTAL {<Name=>}Date),max(TOTAL {<Name=>}Date))*8)


            Capture.PNG

            • Re: Expression results different in Straight and Bar chart
              Amit Saini

              Hi Sunny,

               

              Sorry , still some issue. Please see results for June E is showing 168 , but it should be same as A=176

               

               

               

              Except Sep month , Value of E should be always equal to A,B ,C and D.

               

              PFA!

               

              Regards,

              AS

                • Re: Expression results different in Straight and Bar chart
                  Sunny Talwar

                  Why is that so? I thought you wanted this to the chart... when I select E, 2017, and June... I see 168... why would the chart not show 168 in the bar chart?

                   

                  Capture.PNG

                  I think till now we were only looking at the numbers you wanted, may be it would be better if you can explain your requirement because I am not sure what logic you are using to get to the numbers you have been asking for.

                    • Re: Expression results different in Straight and Bar chart
                      Amit Saini

                      Hi Sunny,

                       

                      I believe best is to tell you what is requirement.

                       

                      Let me try to explain - We are have Name (Employees) as A,B,C,D and E in database (Excel) where they are entering number of documents on some particular Date in a Month.

                       

                      Data Sample:

                       

                        

                      NameDateSheets
                      A9/1/20173
                      A9/2/20171
                      A9/3/20171
                      A9/4/20171
                      A9/5/20171
                      A9/6/20171
                      A9/7/20171
                      A9/8/20171
                      B9/1/20172
                      B9/4/20173
                      B9/6/20176
                      B9/8/20173
                      B9/11/20177
                      B9/13/20175
                      B9/18/20171
                      B9/19/201711
                      B9/20/20178
                      B9/21/20173
                      B9/25/20172
                      B9/26/20175
                      B9/27/20173
                      B9/28/20178
                      B9/30/20172
                      C9/1/20172
                      C9/4/20175
                      C9/5/20172
                      C9/6/20173
                      C9/7/20173
                      C9/8/20171
                      C9/11/20171
                      C9/12/20174
                      C9/14/20172
                      C9/18/20175
                      C9/19/20172
                      C9/20/20177
                      C9/21/20172
                      C9/25/20172
                      C9/26/20177
                      C9/27/20175
                      C9/28/20172
                      D9/12/20171
                      D9/13/20171
                      D9/19/20172
                      D9/20/20171
                      D9/26/20171
                      D9/27/201723
                      D9/28/20171
                      E9/8/201750
                      E6/1/20171
                      E6/1/20172
                      E6/5/20171
                      E6/6/20171
                      E6/6/20174
                      E6/7/20171
                      E6/8/20171
                      E6/9/20171
                      E6/13/20171
                      E6/13/20174
                      E6/14/20171
                      E6/15/20171
                      E6/16/20171
                      E6/19/20171
                      E6/20/20173
                      E6/22/20171
                      E6/22/20172
                      E6/23/20171
                      E6/27/20173
                      E6/28/20171
                      E6/29/20171
                      A6/1/20173
                      A6/2/20172
                      A6/5/20171
                      A6/6/20171
                      A6/7/20171
                      A6/8/20171
                      A6/8/20175
                      A6/12/20171
                      A6/12/20172
                      A6/13/20171
                      A6/15/20171
                      A6/16/20171
                      A6/20/20171
                      A6/20/20172
                      A6/21/20172
                      A6/22/20172
                      A6/27/20174
                      A6/27/20175
                      A6/29/2017

                      1

                       

                      Now what we have to calculate is Productivity of each employee ,which is = Sum(Sheets)/ Total Hours

                       

                      Here we have to consider 8 hour per day , so 40 in a week excluding weekend.

                       

                      Let's take Example where A is having Sheets Date entry starting from 1st Sep and till 8th Sep ,see below

                       

                      So here to calculate productivity =sum(Sheets) is 10 , but Total Hours = All Weekdays*8, which is 168.for whole Sep Month.

                       

                      Total Hours is independent of any Date entry against Sheets , so for all Months it has to be Weekdays*8 :

                       

                      June =22 Working days *8 = 176 and in Sep = 21*8 =168

                       

                      For this first I have created a Master Calendar , which will give me all Dates and then I can use expression :

                       

                      NetWorkDays(Min(Date), Max(Date))*8

                       

                      But I'm having one complication in requirement , one of my Employee Name =E , left organization on 8th Of Sep , so only for this employee my Total Hours should be number of working days till 8th Sep = Yellow Marked 5 Days *8=48

                       

                       

                       

                      and for rest A,B,C,D  Total Hours should remain as Working Day *8 in a Month .

                       

                      I hope this will help!

                       

                      Regards,

                      AS

                  • Re: Expression results different in Straight and Bar chart
                    Sunny Talwar

                    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

                    • Re: Expression results different in Straight and Bar chart
                      Sunny Talwar

                      I have added a new variable for Start Date for an employee... in the sample, I did it for Employee B check it out

                       

                      Capture.PNG

                        • Re: Expression results different in Straight and Bar chart
                          Amit Saini

                          Sorry Sunny , this is wrong.

                           

                          Let me take example of E , as it is easy to explain.

                           

                          When we are having below case of

                          LET vMinDate = num(makedate('2017'));

                          Output for E was '1440' across Year 2017 until 9/8/2017 (For this we have used EndDate Flag)


                          Now suppose if I'm changing LET vMinDate = num(makedate('2016'));

                          Than I'm adding one more extra year , which will for sure change values for all Name , further for E Min(%Date) is 6/1/2017 and as per your logic output is 576  ,which is correct in general.


                          But client want to see Min(%Date) as 1/2/2017 first working day of 2017 and this is the year when Date has been entered against Sheet , so the actual output they looking for is 1440 all the time no matter even if I start my year from 2014.


                           

                          Sorry for too much complications!

                           

                          Regards,
                          AS



                          • Re: Expression results different in Straight and Bar chart
                            Amit Saini

                            Hi Sunny,

                             

                            Thanks once again!

                             

                            They agreed to keep vStartDate as the date min date entered against sheet, so ur solution is working as expected.

                             

                            Regards,

                            AS