Using Match functionality on the results of another expression

[#1]

Hello, I am very new to Qlikview and struggling to get something to work, hoping someone here will be able to help me.

I have the below expression which outputs  '1', '2' or '3' based on the maximum time of a data set.

=sum([Numeric Value]*if([Time]=aggr(nodistinct max([Time]), [Team Name]),1,0))

Team Name        Time           Numeric Value

Team A                 09:00                    1

Team B                 09:00                    2

Team C                 08:00                    2

Team D                 09:00                    2

Team E                 09:00                    3

What I would like to do is write an expression that will tell me if any of the teams contained a specific Numeric Value using the above expression.

If any of the teams reported a '3' I would like to show 'Red'

If there is not a '3' but there is a "2'' I would to show 'Amber'.

Anything else 'Green'

I have tried doing this using IF and Wildmatch  with the previous mentioned expression being used as a Variable  but struggling to get it to work

Thank you for your help in advance

Rhys

[sunny_talwar · Accepted solution]

May be like this:

If(Max(TOTAL <[Team Name]> Aggr(Sum([Numeric Value] * If([Time] = Aggr(NODISTINCT Max([Time]), [Team Name]),1,0)), [Team Name], Time)) = 3, LightRed())