Additions to conditional formatting
Hi
I reckon this should be a simple one. Essentially it's a combination of the logical band with "positive negative" and the continuous linear function.
Say I have a range going from -50 to +200. I'd like all the negative numbers to be in shades of red and the positives in shades of green. So essentially 2 separate linear bands.
This wouldn't be an issue if my range was always symmetrical, but that's never the case...
I've done it somewhat using formulas to calculate, say, a 50% value of max and min, then use that in static bands. Problem is, that this method gives a value in background to null values as well, hence the need to put a filter on top to get rid of these as well. And it wasn't possible to combine this with totals.
Regards
Ove
3 replies
-
Hi Ove,
Is this an enhancement request or a request for help in creating? It isn't clear.
If the former, please submit in the Product Ideas forum, otherwise if you could provide an example visual of what it is you are seeking to build?
Have you looked at the new conditional formatting options in 2020.20?
Regards,
Ian
-
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 or it is non-symmetrical 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
Step 1: Open a grid report in Discover and add the value field to the color drop zone
In the example above the dataset includes a number of outliers to the sales data and the conditional formatting is not evenly distributed through the records
Step 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. In your example midpoint is zero so no parameter is required
Set the parameter to a number and free input. Save the parameter
Step 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
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
-
Thank you Alex! That's really great. I can definitely work with that. Hopefully some of this will come more natively in future releases.
