Richard Pobi Posted December 21, 2018 Share Posted December 21, 2018 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. Case 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 More sharing options...
Gaia Paolini Posted January 8, 2019 Share Posted January 8, 2019 Hi 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. Gaia Link to comment Share on other sites More sharing options...
Richard Pobi Posted February 20, 2019 Author Share Posted February 20, 2019 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now