
1. Re: How do I conditionally color my data using formula?
Manideep Bhattacharyya Apr 4, 2016 12:19 AM (in response to Sameeksha Aithal)Hello Sameeksha  Create a Calculated Field called KPI and use the formula below:
if [C]<([A]+[B])/3 THEN "Red"
elseif [C]>([A]+[B])/3 THEN "Yellow"
elseif [C]>([A]+[B]) THEN "Green"
END
Drag this field in the Color Marks and set the color accordingly.

2. Re: How do I conditionally color my data using formula?
Sameeksha Aithal Apr 4, 2016 1:45 AM (in response to Manideep Bhattacharyya)Hi Manideep,
I am not able to fill the cell with the color after dragging it to color marks. Can you please help me out with it?
I thought of highlight tables. I want "name" to be highlighted as well.

4. Re: How do I conditionally color my data using formula?
Manideep Bhattacharyya Apr 4, 2016 7:51 PM (in response to Manideep Bhattacharyya)If this solve your problem, then please mark this as correct answer and close the thread.

6. Re: How do I conditionally color my data using formula?
Sharad Joshi Apr 4, 2016 9:12 PM (in response to Sameeksha Aithal)Hi Sameeksha,
Can you attach the packaged workbook.
I want to see your data structure as well.
Thanks

8. Re: How do I conditionally color my data using formula?
Sameeksha Aithal Apr 5, 2016 8:01 AM (in response to Sharad Joshi)Hi Sharad,
The data is confidential. So, I can't share the data. Refer to the excel snapshot that I want to replicate in tableau. If you need any further clarifications, let me know.

9. Re: How do I conditionally color my data using formula?
G Marc Turner Apr 5, 2016 11:26 PM (in response to Sameeksha Aithal)I think the solution will depend in part on how your data is structure. If the data is structured like this:
Name Category Value Alpha A1 (value) Alpha B1 (value) Alpha C1 (value) Alpha A2 (value) Alpha B2 (value) Alpha C2 (value) etc the solution could be different than if the data is structured like this:
Name Period A B C Alpha 1 (value) (value) (value) Alpha 2 (value) (value) (value) Alpha 3 (value) (value) (value) With the first data structure, I was able to get fairly close to your example using table calculations and a highlight table:
This solution also works if you break category into two fields, for example a Category (A, B, C) and a time (1, 2, 3). The key is that you have a single measure per row rather than 3 separate measures per row. The only thing I wasn't able to replicate was shading the NAME field based on the formatting of the C columns. I don't believe that Tableau allows you to format/shade the levels of dimension in the header separately. As a work around I added a "status" column to the beginning, assuming that the purpose of the color coding was to draw attention to those that needed attention. (I added the 4th NAME to have an example where all fields were green.) This solution does involve using table calculations to lookup the values in various columns, so it does require C to be every 3rd column. I've attached it as a packaged workbook in v9.3 (since that's the only version I have on this computer).
If your data is structured in a different format, then a different solution will be required.
Marc

Thread 204076.twbx 29.5 KB


10. Re: How do I conditionally color my data using formula?
Sameeksha Aithal May 26, 2016 9:26 AM (in response to G Marc Turner)Hi G,
The data can't be restructured that way. The data is not dependent. It is just a nomenclature that I followed. Have you modified the data that way to create a view like the one you showed here?
I am not able to open the attachment. So, can you please comment the code you wrote under table calculation? 
11. Re: How do I conditionally color my data using formula?
G Marc Turner May 26, 2016 10:01 AM (in response to Sameeksha Aithal)To sort the categories, I used the following calculation to rearrange the numbers and letters into a new dimension:
right([Category],1)+Left([Category],1)
I then used the drop down for [Category] and sorted it by the dimension I just created. This allowed things to be sorted A1, B1, C1, A2, B2, C2, etc.
To format the "C" columns, I used this formula:
[Format Table Calculation]
if min(left([Category],1))="C" then
if sum([Value]) >= zn(lookup(sum([Value]),2)) + zn(lookup(sum([Value]),1)) then "1Green"
elseif sum([Value]) >= ( zn(lookup(sum([Value]),2)) + zn(lookup(sum([Value]),1)) / 3) then "2Yellow"
elseif sum([Value]) < ( zn(lookup(sum([Value]),2)) + zn(lookup(sum([Value]),1)) / 3) then "3Red"
end
END
This formula limits the calculations to the categories that start with "C". It then uses the LOOKUP() function to get the values of the previous 2 cells. You do need to make sure that it is computing along the [Category] dimension. I used this calculation with a highlight table to get set the colors of the different cells. The number was included at the beginning so I could use the MAX() function in the following formula to see the most severe:
[Status]
case lookup(
(Max(
lookup(left([Format Table Calculation],1),2),
Max(
lookup(left([Format Table Calculation],1),5),
lookup(left([Format Table Calculation],1),8)
)
)
) ,FIRST())
when "1" then "GOOD"
when "2" then "CAUTION"
when "3" then "PROBLEM"
end
Unfortunately this formula and approach only works with a fixed number of columns to lookup the values. It might be possible to create a calculation that determines the max across the row regardless of the number of columns using a table calculation.
Hope this helps
marc