Sneak: How to do Advanced Conditional Formatting

The Tips&Tricks Series is produced by the Customer Success Team. It highlights several easy to follow instructions and range from simple tasks to more advanced use cases.

How to do advanced conditional formatting

By default, when adding the color field to the drop zone Pyramid applies a linear continuous dual banded format to the values. If you have outliers in the data this may not produce the desired coloring result. To apply a non-linear format to the data apply this trick using an optional parameter and a formula to extend the conditional formatting capabilities.

1. Open a grid report in Discover and add the value field to the color drop zone.
   In the example right the dataset includes a number of outliers to the sales data and the conditional formatting is not evenly distributed through the records.

   

2. Create a global parameter. The parameter will be used to set the mid-point of the range for conditional formatting. Parameters are optional in this tip but will allow a user to adjust the midpoint on report execution. Alternatively, the median value could be used.
   Set the parameter to a number and free input. Save the parameter

   

3. Create a formula(s) to define the range logic. A good tip is to have the formula evaluate to a number between -1 and +1. With the formula evaluating to 0 at the midpoint.
   An example of this logic is shown below, where numbers greater than or equal to the midpoint are divided by the maximum measure value and values less than the midpoint are divided by the minimum value. Numbers less than the midpoint are multiplied by -1.

   

4. Return to the discover and switch the value in the color drop zone to the new formula created in the previous step. Including the formulate in the discover can hep to check values are calculating correctly but is not needed in the final version. In the example shown, changing the MidPoint parameter value will change the background color conditional formatting.

   

If you have questions about these tips (or improvements) please let us know in a comment.

P.S. We will post previous Tip&Tricks as well as all new ones to keep this information active and available to all users.