Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

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

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-FRISATSUN
A0:154:000
B02:000
C2:0000
D05:000
E00:485:00
F005:00

EXAMPLE:-

STARTEndDIFFFRI TimeSat Time
9/23/201111:45:00 PM 9/24/20114:00:00 AM4:15:0000:15:004:00:00

How to calculate this please provide details

See my sample file.

Rgds

Anand

5 Replies
MVP
MVP

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

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)

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

MVP
MVP

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

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)

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-FRISATSUN
A0:154:000
B02:000
C2:0000
D05:000
E00:485:00
F005:00

Rgds

Anand

MVP
MVP

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

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.

Community Browser