topic Calculation Error in QlikView

Calculation Error

aveeeeeee7en — Tue, 21 Oct 2014 10:19:43 GMT

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

Re: Calculation Error

Tue, 21 Oct 2014 12:34:43 GMT

Hi,

Left join(Table1)

Table2:
Load

weekdays,

Weekday(weekdays) as Weeknumb

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

Re: Calculation Error

jolivares — Tue, 21 Oct 2014 12:48:49 GMT

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

aveeeeeee7en — Wed, 22 Oct 2014 05:34:16 GMT

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

Re: Calculation Error

aveeeeeee7en — Wed, 22 Oct 2014 09:01:33 GMT

Any Suggestions?????

Re: Re: Calculation Error

jolivares — Wed, 22 Oct 2014 13:53:45 GMT

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.

Re: Calculation Error

Wed, 22 Oct 2014 15:18:42 GMT

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.

Re: Re: Calculation Error

jolivares — Wed, 22 Oct 2014 15:42:56 GMT

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

Re: Calculation Error

jolivares — Wed, 22 Oct 2014 16:35:22 GMT

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')

Re: Calculation Error

aveeeeeee7en — Mon, 27 Oct 2014 18:11:09 GMT

Thanks Juan

Your Logic and suggestion worked like a charm.

Thank You very much no.of times. You Made my Day......................... 🙂

Regards

Av7eN

Re: Re: Calculation Error

aveeeeeee7en — Wed, 29 Oct 2014 04:30:11 GMT

Hi Juan

1 case is causing problem.

Could be please suggest what's going wrong???

In Attached Qvw, Red Colored Case is causing problem

Regards

Av7eN