Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

drminaker
New Contributor III

How to average hours between a start and end date?

I'm wondering if anyone can help me with creating a chart that will show hours averaged over the duration of a start and end date?

I understand the basics of the master calendar and have created this. I've also been able to calculate the business days/hours between the start date and end date. My problem is that I can't figure out how to average the estimated hours across a span of months and get the months to show up on the chart.

I've been reading though a lot of the community posts and perhaps IntervalMatch is what I need to use, but not sure how to get that to work.

Thanks!

Source data:

StartDateEndDateEstimatedHoursProjectNameStaff
7/1/201511/30/2015500Project ABill
8/1/201509/30/2015300Project BBob

What I'd like my chart to look like:

b3397c6abf7341dda53884fd4dd6f02d.png

1 Solution

Accepted Solutions
MVP
MVP

Re: How to average hours between a start and end date?

I would consider breaking down the EstimatedHours to the smallest granularity needed using a WHILE loop, as demonstrated here:

Creating Reference Dates for Intervals

SET DateFormat = 'M/D/YYYY';

INPUT:

LOAD Recno() as ID, * INLINE [

StartDate, EndDate, EstimatedHours, ProjectName, Staff

7/1/2015, 11/30/2015, 500, Project A, Bill

8/1/2015, 09/30/2015, 300, Project B, Bob

];

ID_x_Dates:

Load ID,

    EstimatedHours / (EndDate - StartDate + 1) as DailyEstimatedHour,

    Date(StartDate + IterNo() -1 ) as ReferenceDate

Resident INPUT

While IterNo() <= EndDate - StartDate + 1 ;

Here I used a date as finest granularity, which you can easily aggregate to week / month / quarter using a master calendar connected to ReferenceDate. If you only need a Monthly view, you can also change the code to iterate over Months, not dates.

4 Replies

Re: How to average hours between a start and end date?

You could try something like this:

sum(EstimatedHours) / count(distinct Month)

if you have connected your StartDate and EndDate with the master calendar: IntervalMatch.

- Marcus

MVP
MVP

Re: How to average hours between a start and end date?

I would consider breaking down the EstimatedHours to the smallest granularity needed using a WHILE loop, as demonstrated here:

Creating Reference Dates for Intervals

SET DateFormat = 'M/D/YYYY';

INPUT:

LOAD Recno() as ID, * INLINE [

StartDate, EndDate, EstimatedHours, ProjectName, Staff

7/1/2015, 11/30/2015, 500, Project A, Bill

8/1/2015, 09/30/2015, 300, Project B, Bob

];

ID_x_Dates:

Load ID,

    EstimatedHours / (EndDate - StartDate + 1) as DailyEstimatedHour,

    Date(StartDate + IterNo() -1 ) as ReferenceDate

Resident INPUT

While IterNo() <= EndDate - StartDate + 1 ;

Here I used a date as finest granularity, which you can easily aggregate to week / month / quarter using a master calendar connected to ReferenceDate. If you only need a Monthly view, you can also change the code to iterate over Months, not dates.

drminaker
New Contributor III

Re: How to average hours between a start and end date?

Thanks so much for this. I applied it to my real data and it works great! As Marcus mentioned, I was sure the solution had something to do with intervals, but just wasn't sure how it worked.

Another question for you -- if I wanted this to span the minutes/hours only over work days where would be the best place to apply Networkdays function? The loadscript, master calendar, custom dimension?

Thanks!

R.

drminaker
New Contributor III

Re: How to average hours between a start and end date?

Thanks for pointing me to that link. I'm determined to become expert on how all this works!

R.