Jump to content
We've recently updated our Privacy Statement, available here. ×

Case statement

Richard Pobi

Recommended Posts

I want to write a case statement that will give me the RESULT column on the attach document. I have three colors (Green, Red and Yellow), ID column is duplicates (4 rows per ID). The color conditional formatting is based on the Amount column. From the Color column, if the ID has all the three colors then that ID is Red. If the ID has only Yellow and Green then Yellow should represent that ID. If the ID has only Green color then ID should be Green. Note, Red color override all the colors, next is Yellow before Green.


when ID=101 AND Color=Red AND Color=Yellow AND Color=Green then Red

when ID=102 AND Color=Yellow AND Color=Green then Yellow

Else Green

Can I use the OVER function if yes, how should I write it Thanks is advance for your assistance

Link to comment
Share on other sites

  • 3 weeks later...



go to Edit | Column Properties and select Color.

go to the Sort Order tab

select Custom Sort Order

Configure as Red, then Yellow, then Green.

Press OK.


This gives you an ordering of the Color column so that Red is the minimum, followed by Yellow then Green.

Then create a calculated column as:

Min() OVER ([iD])

This should give you an equivalent of the Result you wanted.


Link to comment
Share on other sites

  • 1 month later...

Gaia, thanks for your response. Actually your logic is correct, but I have taken care of the issue already. I created a new calculated column (col1) where White = 1, Yellow = 2 and Red = 3. After that I created a second calculated column (col2) as Max (col1) OVER (ID). The next thing is uniquecount (ID) and also Limit Data by the numbers. Limit data (col2) =1, did same for 2 and 3.


Thanks for your input.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...