Qlikview Background Colour expression

[bobbydave]

Hi guys

I have a table

Group	Avg Time
Team A_x	20
Team A_y	10
Team A_z	40
Team B_a	5
Team C_a	2

Avg Time is made up of an expression that sums(Total Time)/count(Volume)

So what I need to do is do is show Avg Time in either Red or Green based on below.

     for anything related to Team A_* > 4, vRed,

          for anything related to Team B_* > 1, vRed,

               for Team C_* > 2, vRed,

                    vGreen.

I've used if statements and set analysis but it doesnt like when I use Avg Time in the Set Analysis

     if( sum ({< Group = {'Team A_*'} >} Avg Time) > 4, vRed,

          if( sum( {< Group = {'Team B_*'} >} Avg Time) > 1, vRed,

                   if( sum( {< Group = {'Team C_*'} >} Avg Time) >2, vRed,

                              vGreen)))))

I've even expanded on Avg time and used the full expression instead but set analysis doesnt like the sum(Total Time)/count(Volume) expression within it.

Any ideas?

Appreciated.

[#2 · Accepted solution]

Hi Bobby

As 'Avg Time' has a space in it, I think you need to wrap it in square brackets ie. [Avg Time]

Also, to do Total Time / Volume you would need to create two separate expressions, both with set analysis ie.

Sum({<Group={'Team A_*'}>}[Total Time]) / Count({<Group={'Team A_*'}>}Volume)

Let me know if that works

James

[bobbydave]

I didnt realise Set Analysis would have to be made up of two parts but makes sense as why to do so.

Thanks. This resolved my issue.