Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Avg in background color of expression

Hi, i want to show a different background color if number of meetings per week is over or below let's say 30% of the average per week. I can't find a way to get the right expression in the background color for the expression in a pivot table. Help much appreciated, Zach.

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Avg in background color of expression

Try this expression:

if(count(MeetingID)> 0.7* avg(total <RoomID> aggr(count(MeetingID),RoomID,Calender.YearWeek))

and count(MeetingID)< 1.3* avg(total <RoomID> aggr(count(MeetingID),RoomID,Calender.YearWeek))

  ,color(2))


talk is cheap, supply exceeds demand
4 Replies
MVP & Luminary
MVP & Luminary

Re: Avg in background color of expression

See attached qvw. The solutions uses a variable to store the 30% of average value. The background color expression uses the variable for the calculation.


talk is cheap, supply exceeds demand
Not applicable

Re: Avg in background color of expression

Hi, thanks for your help. I'm not there yet. What i need is an average calculation per row (per RoomID) over the weeks. For example: for RoomID 100 the numbers 10, 4 and 3 (the first three weeks) should have the red background color. This, since the average is 6 (24/4) and the upper bandwidth is 6*1.3=7.8 and the lower bandwidth is 6*0.7=4.2. So only the 7 (week 2013-5) should have a normal background color. Is this doable, that each row has it's own average inside the pivot table? Thanks again, Zach

MVP & Luminary
MVP & Luminary

Re: Avg in background color of expression

Try this expression:

if(count(MeetingID)> 0.7* avg(total <RoomID> aggr(count(MeetingID),RoomID,Calender.YearWeek))

and count(MeetingID)< 1.3* avg(total <RoomID> aggr(count(MeetingID),RoomID,Calender.YearWeek))

  ,color(2))


talk is cheap, supply exceeds demand
Not applicable

Re: Avg in background color of expression

Hi, perfect. I wouldn't have come up with this formula! Thanks, Zach.

Community Browser