Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
aveeeeeee7en
Specialist III
Specialist III

Logic Error

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:

 

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

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

Kindly, someone help. It's a tricky logic.

Regards

Av7eN

23 Replies
Anonymous
Not applicable

Hi,

1) you don't need the calendar to be stored with the fact table, this was only for testing if working days match corresponding week days, so you can drop this table

2) right, I modified the script to fix this

Hope this helps!

Best

Stefan

aveeeeeee7en
Specialist III
Specialist III
Author

Yes,  the Logic is now working Perfectly for all scenarios. No issues left.

This is something i called as incredible stuff.......

Many-2 thanks to you man. You are the Master. Thanks for devoting your valuable time and mind into my issue. It was pleasure working with you. I have learnt many new things from you which I'll remember & keep with me for a Lifetime.

Regards

Av7eN

Anonymous
Not applicable

Hi,

thank you for your compliments. But please open a new topic for this issue, so others will help you

Best regards

Stefan

aveeeeeee7en
Specialist III
Specialist III
Author

Hi Stefan Sir

I need your help on this post:

Exclude Holidays & their Time from Running Logic...............!!

If possible, kindly look into this.

Regards

Av7eN