Applying conditional formatting on Project Status

This recipe covers how to create a custom Advanced Analytics report using conditional color formatting for Project Status.

What's needed

• Skill level: Beginner
• Time needed: 10 minutes
• Permission profile required: Designer (See Getting Started with Advanced Analytics)
• Data required: Project Status

Creating the report in Advanced Analytics

1. In Advanced Analytics, open or create a Dashboard where you want to add the new widget. 
2. In the dashboard, under New Widget click on '+ Select Data' to open the data browser.
3. In the Add a Field window, search for 'Project Name'. 
4. Click on the purple '+' button once more to add additional data and search for 'Project Status Color'.
   
5. In the Visualization settings, select the 'Pivot' chart type icon to adjust the report display to achieve cleaner, more transparent view of the data.
   
6. Click on Advanced Configuration at the lower left of the dashboard.
   
7. Click '+' next to Values in the left side panel. 
   
8. In the 'Add a Field' window, enter 'Project Status Color' and click to select it.
   
9. In the left side panel under Values, click the pencil icon to open the formula editor. 
   
10. In the top part of the new window, replace the formula by the one below.

    SUM(
    1*(COUNT([Project Status Color]),[Project Status Color1])
    +
    2*(COUNT([Project Status Color]),[Project Status Color2])
    +
    3*(COUNT([Project Status Color]),[Project Status Color3])
    )

11. In the formula, hover the mouse over [Project Status Color1] and click. Choose Edit Filter, select Amber, and click OK. Do the same with the two other colour filters: select Green for [Project Status Color2], then select Red for [Project Status Color3].
12. In the left side panel, under Values, click the white box for 'Change values colors'.
    
13. In the Formula > Color window, select Conditional.
    
    
14. In the first condition row, change the operand to '='. 
15. Enter the value '1' in the field to the right. 
16. Click the color swatch to open the color selector and select yellow.
17. In the next condition row, change the operand to '='.
18. Enter the value '2' in the field to the right. 
19. Click the color swatch to open the color selector and select green. 
20. At the top of the window, click '+Add condition' to add a third row. 
21. Change the operand to '='.
22. Enter the value '3' in the field to the right. 
23. Click the color swatch to open the color selector and select red. 
24. Click OK.
25. Your Formula column will now appear shaded by the color that corresponds to the project's status color. 
    
26. Click Apply. 
27. Your dashboard is now ready to view.