Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi Niels,
Sounds like you'd want to look up IntervalMatch(). There should be some examples floating around the community
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
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.