topic Working days hour calculation with non working days (Sat and Sun) in QlikView

Working days hour calculation with non working days (Sat and Sun)

its_anandrjs — Sat, 24 Sep 2011 10:01:12 GMT

Hi,

I have a Query i think it is so easy but i am stuck on it little bit I use Interval match on that it works but not perfectly.

Query is i want to calculate work hours between two times Start and End, i am able to calculate hour difference but not accurate see sample example like

	MON-FRI	SAT	SUN
A	0:15	4:00	0
B	0	2:00	0
C	2:00	0	0
D	0	5:00	0
E	0	0:48	5:00
F	0	0	5:00

EXAMPLE:-

START	End	DIFF	FRI Time	Sat Time
9/23/201111:45:00 PM	9/24/20114:00:00 AM	4:15:00	00:15:00	4:00:00

How to calculate this please provide details

See my sample file.

Rgds

Anand

Working days hour calculation with non working days (Sat and Sun)

swuehl — Sat, 24 Sep 2011 10:04:37 GMT

Anand,

it seems that I am stucked too...

Could you point me to the inaccuracy in your example?

Re: Working days hour calculation with non working days (Sat and Sun)

its_anandrjs — Sat, 24 Sep 2011 10:11:53 GMT

Hi,

My point is in example a 00:15 mins is for Friday and 4:00 hours for saturday when i use Interval match it shows 4:15 but 15 mins are for friday i want only Saturday time and if it is Sunday so that time.Because 15 mins are come under working days which is 15 mins we are not take it under 24th date but it is of 23th date.

What is the best way to achieve this i try so many ways but not get acurate result.

Let me know

Rgds
Anand

Re: Working days hour calculation with non working days (Sat and Sun)

swuehl — Sat, 24 Sep 2011 10:57:21 GMT

Anand,

not sure what the best solution is, but I used a while loop over the records in the script to get a record for each single weekday.

Then just parsed the hours worked each day into hoursworked using some if()s.

Could be maybe more elegant, but it works.

Stefan

Re: Working days hour calculation with non working days (Sat and Sun)

its_anandrjs — Sat, 24 Sep 2011 17:53:50 GMT

Stefan,

Thanks so much its good way that you provide but any other way we can generate and show the values like in this example, In this way i want output some thing like this.

	MON-FRI	SAT	SUN
A	0:15	4:00	0
B	0	2:00	0
C	2:00	0	0
D	0	5:00	0
E	0	0:48	5:00
F	0	0	5:00

Rgds

Anand

Working days hour calculation with non working days (Sat and Sun)

swuehl — Sat, 24 Sep 2011 18:01:09 GMT

Sure,

use

=if(Day < 5,'Mon - Fri', Day)

as calculated dimension in the chart (or similar in the script).

This gives you exactely your above table.