topic Re: Average hours per workday in QlikView

Average hours per workday

Thu, 27 Feb 2014 07:51:52 GMT

Hi all,

I'm relatively new to QlikView and was wondering if there is a method to aggregate all workdays to an average working hours, and then show them in a Gantt-chart style.

I've searched the community already and couldn't find the answer, probably because I can't formulate my question adequately.

I already got the following: It shows all dates in day names date(<datefield goes here> 'WWWW') over an period of some weeks.

The expression is a simple SUM of total work time (end-start time) with an offset on the start time.

But what i want is the following (I made a selection of last week data)

The data is in Dutch and represents the weekdays monday-friday.

I sure hope it's possible!

Kind regards,

Job van den Berg

Re: Average hours per workday

Nicole-Smith — Thu, 27 Feb 2014 18:21:36 GMT

For your dimension:

text(WeekDay(YourDateField))

Re: Average hours per workday

santharubban — Thu, 27 Feb 2014 18:49:55 GMT

try this, networkdays returns the number of working days (Monday-Friday)

networkdays ('2007-02-19', '2007-03-01') returns 9

Re: Average hours per workday

Gysbert_Wassenaar — Thu, 27 Feb 2014 19:10:09 GMT

date(<datefield goes here> 'WWWW') will only change the formatting of the date to the day name. The underlying values are still the individual dates. What you need is a weekday field as dimension. You can create that in the script:

MapDays:

mapping load * inline [

Weekday, Name

0, Maandag

1, Dinsdag

2, Woensdag

3, Donderdag

4, Vrijdag

];

WorkDays:

Load

Date

, StartTime

, EndTime

, interval(EndTime-StartTime,'hh:mm') as WorkHours

, applymap('MapDays',weekday(Date)) as Weekday

from ...mysource...;

Re: Average hours per workday

Clever_Anjos — Thu, 27 Feb 2014 20:05:11 GMT

Maybe an aggregation like

AVG(

AGGR(

SUM(time),

yourdimension

)

Re: Average hours per workday

whiteline — Thu, 27 Feb 2014 20:51:09 GMT

Hi.

The problem is that date() function changes the text format of dual data type (date is dual, both text and numeric). The numerical date remains unchanged. So you see separate dimension value for each maandag (QV distinguishes the values by numeric part).

To solve the problem you should have the numeric values of each maandag to be equal. The functions like Month, WeekDay etc. do that. Then you can use Date() function to set the format you like:

=Date(WeekDay(Date), 'WWWW')