Sum Value outside of selection

[#1]

Hi There,

For some reason I can't do this in QlikView...

What I'm trying to do is this...

I have a calendar table:

Key_Date, WorkingHoursInDay

01/01/2012, 8

02/01/2012, 0

03/01/2012, 8

04/01/2012, 8

05/01/2012, 8

JobID, JobStart, JobEnd

7, 01/01/2012, 01/03/2012

8, 04/01/2012, 05/01/2012

I would like a chart that has:

Job Number, JobStart, JobEnd And then Sum(WorkingHoursInDay) Between JobStart And JobEnd

e.g.

7, 01/01/2012, 01/03/2012, 16 Hours

8, 01/01/2012, 05/01/2012, 32 Hours

Any ideas on how to do this?

Thanks

[Anonymous]

Hi Niels,

Sounds like you'd want to look up IntervalMatch(). There should be some examples floating around the community

[jonathandienst · Accepted solution]

Hi

You can do this in a chart or straight table using the JobID as dimension, and the expression:

Sum(Aggr(Sum(If(KeyDate >= JobStart And KeyDate <= JobEnd, WorkingHours)), JobID, KeyDate))

See example attached.

Hope that  helps

Jonathan

[#4]

Thanks Jonathan,

That's exactly right!

Sadly I can't get it working with my real world example. I had something similar but I keep getting null returned.

I'll mark it as correct as it works perfectly as far as the question is phrased.

Thanks again.