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: 
qliklizzy
Creator II
Creator II

visual cues expression

Hi

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

1 Solution

Accepted Solutions
sunny_talwar

Try using background expression rather than visual clues

Capture.PNG

View solution in original post

12 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
qliklizzy
Creator II
Creator II
Author

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?

sunny_talwar

How about this may be

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

qliklizzy
Creator II
Creator II
Author

thanks sunny

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

qliklizzy
Creator II
Creator II
Author

hi

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

sunny_talwar

I don't see any attachment?

qliklizzy
Creator II
Creator II
Author

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

sunny_talwar

What is not right about this?

Capture.PNG

qliklizzy
Creator II
Creator II
Author

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.