10 Replies Latest reply: Oct 29, 2014 12:30 AM by AVIRAL NAG RSS

    Calculation Error

    AVIRAL NAG

      Hi All

       

      I am doing Calculation of Numbers based on Logic.

      The Correct Calculation are Green in Color and Wrong Calculation is Red in Color.

       

      I am calculation Time Difference between CreationTime & OutTime based on:

       

      1. The Creation Time & End Time should be considered between StartTime & EndTime.

      2. Eg. 1268170, 1274294, 1300082, 1435122 - Consider only Mon-Fri and exclude Saturday & Sunday.

       

      I have implemented logic of Weekdays in the Logic.

       

      Script_1.png

       

      Kindly, see the Attachment.

       

       

      Regards

      Av7eN


        • Re: Calculation Error

          Hi,

           

          Left join(Table1)

           

          Table2:
          Load

                  weekdays,

                  Weekday(weekdays) as Weeknumb

          resident Table1 where Match(weekdays,'Mon','Tue','Wed','Thu','Fri');

          • Re: Calculation Error
            Juan Olivares

            Sometimes when you are trying to solve something and understand the problem is difficult to explain.

             

            Please, give me the exact procedure of how get 38:18 in your qvw and the other red codes.

              • Re: Re: Calculation Error
                AVIRAL NAG

                Hi

                 

                I am explaining 2 scenarios how the Calculation is done:

                 

                Case No.1:


                WRONG dURATION.png

                 

                It's Creation Time is  25/3/2014 1:11:24 PM

                &   OutTime             is   31/3/2014 7:40:00 PM

                 

                If you Reload Attached qvd DataMainData, than you'll see its Working weekdays are different for individual Numbers.

                For some of the cases Working Weekdays are Mon - Fri

                & For some of the cases Working Weekdays are Mon - Sat

                 

                It's Working Weekdays are Mon - Fri

                 

                 

                Also, calculate difference b/w OutTime & CreationTime by considering Start Time & End Time i.e. different individual Numbers

                 

                25 = 4:18:36 hrs ( Hours b/w 9:00 to 17:30)   Calculation=(17:30 - 1:11:24 = 4:18:36)

                26 = 8:30 hrs  ( Hours b/w 9:00 to 17:30)   Calculation=(17:30 - 9:00= 8:30:00)

                27 = 8:30 hrs  ( Hours b/w 9:00 to 17:30)   Calculation=(17:30 - 9:00= 8:30:00)

                28 = 8:30 hrs  ( Hours b/w 9:00 to 17:30)  Calculation=(17:30 - 9:00= 8:30:00)

                29 = 0 hrs   ( Saturdayto be excluded since Working Weekdays are Mon - Fri)

                30 = 0 hrs  ( Sunday to be excluded since Working Weekdays are Mon - Fri)

                31 = 8:30 hrs  ( Hours b/w 9:00 to 17:30)  Calculation=(17:30 - 9:00= 8:30:00)

                 

                Sum of All = 4:18:36 hrs + 8:30 hrs + 8:30 hrs + 8:30 hrs + 8:30 hrs = 38:18:36 hrs

                 

                 

                Case No.2:

                 

                Right Duration.png

                 

                It's Creation Time is  30/3/2014 11:46:39 AM

                &   OutTime             is   5/4/2014 5:00:00 PM

                 

                If you Reload Attached qvd DataMainData, than you'll see its Working weekdays are different for individual Numbers.

                For some of the cases Working Weekdays are Mon - Fri

                & For some of the cases Working Weekdays are Mon - Sat

                 

                 

                It's Working Weekdays are Mon - Sat

                 

                 

                Also, calculate difference b/w OutTime & CreationTime by considering Start Time & End Time i.e. different individual Numbers

                 

                30 = 0 hrs  ( Sunday to be excluded since Working Weekdays are Mon - Sat)

                31 = 8:30 hrs ( Hours b/w 9:30 to 18:00)   Calculation=(18:00 - 9:30 = 8:30:00)

                1  = 8:30 hrs ( Hours b/w 9:30 to 18:00)  Calculation=(18:00 - 9:30 = 8:30:00)

                2  = 8:30 hrs ( Hours b/w 9:30 to 18:00)   Calculation=(18:00 - 9:30 = 8:30:00)

                3  = 8:30 hrs ( Hours b/w 9:30 to 18:00)   Calculation=(18:00 - 9:30 = 8:30:00)

                4 = 8:30 hrs ( Hours b/w 9:30 to 18:00)   Calculation=(18:00 - 9:30 = 8:30:00)

                5  = 7:30 hrs ( Hours b/w 9:30 to 18:00)   Calculation=(5:00 - 9:30 = 7:30:00)


                Sum of All = 8:30 hrs+ 8:30 hrs + 8:30 hrs + 8:30 hrs + 8:30 hrs + 7:30 hrs = 50:00:00 hrs