Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Kindly, see the Attachment.
Regards
Av7eN
Again... Cerate 3 columns. I correct the values for you.
Start:
Sum({<[Process Flag]={'START'}>}
If(Timestamp(CreationTime)>Timestamp(Floor(CreationTime)+Time(EndTime)),0,Timestamp(Floor(CreationTime)+Time(EndTime))-Timestamp(CreationTime)))*24*60
Other:
Sum({<[Process Flag]={'OTHER'}>}[Total Number of Working Hours])
End:
Sum({<[Process Flag]={'END'}>}
If(Timestamp(OutTime)>Timestamp(Floor(OutTime)+EndTime),Timestamp(Floor(OutTime)+EndTime),Timestamp(OutTime))-Timestamp(Floor(OutTime)+StartTime))*60*24
Duration:
Interval((([Start] + [Other] + [End]))/24/60, 'hh:mm')
Hi,
Left join(Table1)
Table2:
Load
weekdays,
Weekday(weekdays) as Weeknumb
resident Table1 where Match(weekdays,'Mon','Tue','Wed','Thu','Fri');
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.
Hi
I am explaining 2 scenarios how the Calculation is done:
Case No.1:
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:
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
Any Suggestions?????
I suggest that you calculate as follows in the script:
1. Days between multiply by the difference of outitme - intime (use the function networkdays)
2. Firstdate - dayend
3. Lastdate - dayend
Sum all, as you describe in your example.
Later i can sent you an example with small data, but now i am very busy.
Please do like this for exact calculation.
1. Calculate seconds b/n 9.30 AM to 6PM --> 30600
2. Calculate Network days b/n Start Time & End Time & Multiply with working seconds
NetWorkDays( Floor(StartTime) , Floor(EndTime) ) * 30600
3. Then calculate the First day & last day seconds.
I see your data again, check total number of working hours with the flag "END". In this case you need to calculate this end hours asking if the OutTime > OUTTIME, because this time for the case is out of the end time.
correct that and check again
Again... Cerate 3 columns. I correct the values for you.
Start:
Sum({<[Process Flag]={'START'}>}
If(Timestamp(CreationTime)>Timestamp(Floor(CreationTime)+Time(EndTime)),0,Timestamp(Floor(CreationTime)+Time(EndTime))-Timestamp(CreationTime)))*24*60
Other:
Sum({<[Process Flag]={'OTHER'}>}[Total Number of Working Hours])
End:
Sum({<[Process Flag]={'END'}>}
If(Timestamp(OutTime)>Timestamp(Floor(OutTime)+EndTime),Timestamp(Floor(OutTime)+EndTime),Timestamp(OutTime))-Timestamp(Floor(OutTime)+StartTime))*60*24
Duration:
Interval((([Start] + [Other] + [End]))/24/60, 'hh:mm')
Thanks Juan
Your Logic and suggestion worked like a charm.
Thank You very much no.of times. You Made my Day......................... 🙂
Regards
Av7eN