topic Re: visual cues expression in QlikView

visual cues expression

qliklizzy — Mon, 05 Mar 2018 11:00:13 GMT

I have some expressions that figure out some timings, but I really need help with getting some highlight on them...

Op time which sums up all the average op time of each procedure that's booked into a session.

op time = Interval(sum( aggr( AvgOp, MPI, AvgOp)),'hh.:mm')

but I need to add 5 minutes turnaround time to each op booked in just to account for any gaps

so my count of ops is: count(EpisodeID)

so I use this to get that into a time plus 5 mins: interval(count(EpisodeID)*5 /24/60,'hh:mm')

then I have session length which is: interval(210/24/60,'hh:mm')

so I was going to use this to total up all the time that will be used in a session: avg op time plus turnaround time

Interval(sum( aggr( AvgOp, MPI, AvgOp)),'hh.:mm') +interval(count(EpisodeID)*5 /24/60,'hh:mm')

so in visual cues i wanted to highlight areas in red if there is no more space to add any more ops:

but this doesn't work - any ideas?

=if(interval(count(EpisodeID)*5 /24/60,'hh:mm')+ interval(sum( aggr( AvgOp, MPI, AvgOp)),'hh.:mm') > interval(210/24/60,'hh:mm') ,1)

I have all the expressions working separately ok - just cant get the highlight to work

thanks

Liz

Re: visual cues expression

jonathandienst — Mon, 05 Mar 2018 11:14:27 GMT

Your expression probably does work but it may not be formatted correctly. Remember Interval() is merely a formatting function and the underlying value is not affected. So the first expression should probably be

=Interval(sum(aggr(AvgOp, MPI, AvgOp)) + count(EpisodeID) *5/24/60, 'hh:mm')

And the condition

=If(sum(aggr(AvgOp, MPI, AvgOp)) + count(EpisodeID) *5/24/60 > 210/24/60, 1, 0)

This does, of course, assume that the Aggr() is working correctly in this context

Re: visual cues expression

qliklizzy — Mon, 05 Mar 2018 11:50:18 GMT

thanks for the quick reply.

yes the expression works ok and I've changed it to the one above; much neater, but the condition still doesn't work.

its the condition I put into the visual cues; they all highlight red even those that do not go over the session length.

am I using the visual cues incorrectly?

Re: visual cues expression

sunny_talwar — Mon, 05 Mar 2018 11:59:17 GMT

How about this may be

frac(Count(EpisodeID)*5/24/60)+ frac(Sum(Aggr(AvgOp, MPI, AvgOp))) > 210/24/60

Re: visual cues expression

qliklizzy — Mon, 05 Mar 2018 12:08:15 GMT

thanks sunny

this also just returns highlight to all times. I will try to upload what I have started.

Re: visual cues expression

qliklizzy — Mon, 05 Mar 2018 12:15:29 GMT

I have scrambled the app and attached it.

I am just trying to create a calendar forward view of our theatre capacity to help book and plan, so adding red highlight to full lists and green to those that need filling etc. would help. I wasn't sure how best to do this so a pivot table looked the best option.

thanks for your help

Re: visual cues expression

sunny_talwar — Mon, 05 Mar 2018 12:17:24 GMT

I don't see any attachment?

Re: visual cues expression

qliklizzy — Mon, 05 Mar 2018 12:26:54 GMT

I have edited the post, not sure what happened but there is now two

Re: visual cues expression

sunny_talwar — Mon, 05 Mar 2018 12:29:27 GMT

What is not right about this?

Re: visual cues expression

qliklizzy — Mon, 05 Mar 2018 12:31:40 GMT

on Tuesday 6th march the list is full. they have 12 ops in with avg op time of 3:36 whent he list is 3:30 but also I add 5 mins to each op to allow or turnaround times.

the other lists should be green as they are not over 3:30 hours.

Re: visual cues expression

sunny_talwar — Mon, 05 Mar 2018 12:35:54 GMT

Try using background expression rather than visual clues

Re: visual cues expression

qliklizzy — Mon, 05 Mar 2018 12:39:38 GMT

arhh ok thanks!

why is that then?, definitely works!

thank you!

Re: visual cues expression

raynac — Wed, 22 May 2019 15:16:00 GMT

Sunny, yet again, you have provided a solution for an issue I was experiencing!! Don't know what I'd do without you.