Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sum Value outside of selection

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

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

3 Replies
Anonymous
Not applicable
Author

Hi Niels,

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

jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

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.