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
• Plan required: Forecast Lite, Pro or Plus plan + Advanced Analytics
• 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 of the Edit Formula window, enter the formula below and click OK.

    SUM(
    1*([# of unique Project Status Color],[Project Status Color])
    +
    2*([# of unique Project Status Color1],[Project Status Color1])
    +
    3*([# of unique Project Status Color1],[Project Status Color2])
    )

11. In the left side panel, under Values, click the white box for 'Change values colors'.
    
12. In the Formula > Color window, select Conditional.
    
    
13. In the first condition row, change the operand to '='. 
14. Enter the value '1' in the field to the right. 
15. Click the color swatch to open the color selector and select yellow.
16. In the next condition row, change the operand to '='.
17. Enter the value '2' in the field to the right. 
18. Click the color swatch to open the color selector and select green. 
19. At the top of the window, click '+Add condition' to add a third row. 
20. Change the operand to '='.
21. Enter the value '3' in the field to the right. 
22. Click the color swatch to open the color selector and select red. 
23. Click OK.
24. Your Formula column will now appear shaded by the color that corresponds to the project's status color. 
    
25. Click Apply. 
26. Your dashboard is now ready to view.