Suppressing zeros in line charts

ryan_kohn
ryan_kohn
edited August 19 in Best Practices

Problem statement

Let's consider plotting a line chart for the following data set:

ryan_kohn_6-1659135418973.png

For this example, I want to exclude zeros from my chart so that users can focus their attention on months where there are actual values. When I try to plot this line chart with the default configuration, this is what I see:

ryan_kohn_9-1659135938252.png

Let's look at what options we have available for suppressing those zeros.

Solution options

Line item format setting: Zero format

Your first approach may be to change the zero format in the modeling experience to show blanks where there are zeros; however, per the Anapedia entry on Handling Zeros in Charts, this only impacts grids, not charts.

Chart configuration: Trailing zeros

If you know all your zeros will be at the end of your data set (for example, your forecast values will all be 0 when you are plotting actuals) then you can leverage native functionality to disable the Plot trailing zeros option in the Chart configuration.

ryan_kohn_7-1659135469877.png

ryan_kohn_8-1659135556566.png

However, this does not help you with any other zero values (like leading zeros or zeros in the middle of your data).

Suppressing all zeros in line charts with NaN

So, how do we solve this visualization challenge?

You can actually "trick" Anaplan to avoid plotting a zero value by replacing your 0's with NaN's. While you can't manually enter NaN into a line item, you can write a formula that will return NaN.

We will be leveraging System modules in this example. As a refresher, we want to use System modules in case we need this logic in other places. This will help us to avoid repetition (see Planual 2.02-03) and daisy chaining (see Planual 2.02-19).

Step 1: Add a NaN value line item to your Constants module

I suggest adding a NaN value to your constants module (a type of System module) so you can use it across your model as needed.

Grid view:

ryan_kohn_10-1659136382686.png

Blueprint:

ryan_kohn_11-1659136382691.png

While there are various options for formulas for that return NaN, I have used the one below:

DIVIDE(0,0)

Step 2: Check for zeros

Create a System module to check for zero values. 

Blueprint:

ryan_kohn_3-1659135106162.png

Step 3: Replace zeros with NaN values

Add a new line item to your Output module that replaces zeros with NaN values.

Grid View (including formula):

ryan_kohn_13-1659136887207.png

Formula:

IF SYS Suppress Zeros.Value is Zero? THEN SYS Model Constants.NaN ELSE Value

Step 4: Update the line chart to point to the new line item

Final output in the UX comparing the two line items:

ryan_kohn_12-1659136799785.png

Conclusion

Leveraging NaN values allows you to effectively suppress zeros from your line charts. Feel free to post any questions, comments, or additional suggestions below!

Additional resources/credits

  • The idea to use NaN was shared with me by @R_Goza. Additionally, @ChrisAHeathcote shared the overall approach previously in another post on Community.
  • Anapedia offers another suggestion for Handling Zeros in Charts to use a very small number instead of NaN.
  • There is an open Idea on the Idea Exchange for this topic:  

https://community.anaplan.com/t5/Idea-Exchange/Do-not-show-zero-values-in-charts/idi-p/51403

Tagged:

Categories