Chart creation - Omitting Null values

Hi Team,

i would like to create a chart wherein the zero values need not be plotted on a graph.In other words, I would like to create a chart excluding the null / zero values,. Please provide some guidance on the same. Existing chart screenshot is attached herewith for your ready reference.

Thanks and regards,

Aishwarya

Chart-Omit_null_42661.jpg

Accepted answers

LipChean_Soh

Hi Aishwarya,

To create a NaN, you need to write Divide(0,0).

Inserting NaN in the module, the following graph is derived.

Screen Shot 2019-03-21 at 8.22.02 AM.png

I prefer having 0 in my graph, because it's reflecting the true value.

But if you're still looking for a graph that bypasses 0, and connects only values, i think there's 2 choices:

1. Create a module that filters out zero, create a line chart out of the non zero values. Then at the bottom of the line chart, create a module with all values (including the zero), and use conditional formatting to highlight these zeroes, so that users can still tell when the zeroes happen.

2. The other option is to mathematically derive a value to replace the zero, so that we don't get what we see above in the NaN graph, eg if Jan = 1, Feb = 0, Mar = 5; then derive 3 for Feb so that there's a smooth line connecting Jan to Mar. However this approach has 2 problems, i.e. first of all, it sends the wrong message because i'm seeing Feb = 3 in the graph, but the module is saying 0. Secondly it's not easy to derive these values, especially when there could be more than 1 zero between values, eg Jan = 1, Feb = 0, Mar = 0, Apr = 0, May = 5.

Thanks,

LipChean

All comments

LipChean_Soh

Hi Aishwarya,

You can create a filter that only shows list members with values, then create a Saved View.

You can then create a graph out of this Saved View, and the graph should reflect accordingly, i.e. show list members with values in the graph.

Please note that this method only works with custom list, and not with Standard Time.

Thanks,

LipChean

Aishwarya

Thank you for the solution, we shall try it out and provide feedback!

Aishwarya

Hi LipChean,

I have tried your solution . Filtering the zero values is also resulting in hiding of the concerned timeperiods on the x axis. I would like to know if there is a solution wherein the x axis would display all the time periods irrespective of the quantities against them; only the chart plot needs to be blank for zero values.

Thanks and regards,

Aishwarya

ben_speight

I wonder how not-a-number (NaN) is treated by the chart. If it omits them then you just need map zero to NaN. You can create a NaN using DIVIDE with zero as the second argument.

LipChean_Soh

Hi Aishwarya,

What does 'only the chart plot needs to be blank for zero values' mean?

Thanks,

LipChean

Aishwarya

Thank you , I shall check the same the revert to you if it works

Aishwarya

Hi LipChean,

I mean the chartline should be truncated at points where values are zero ( though their corresponding time periods would still remain plotted on x axis). When we filter the values, the corresponding time periods also get hidden. We would still want such time periods also to be displayed on the x axis.

Thanks and regards,

Aishwarya

LipChean_Soh

Hi Aishwarya,

Do you mean for points where the value = 0, you still want to show the time, but not the value = 0?

For example, assuming we have Jan = 1, Feb = 0, Mar = 5; you want to show a graph where Jan, Feb and Mar are still on the x axis; and the line connects from value = 1 to value = 5, instead of 1 to 0 to 5?

Thanks,

LipChean

Aishwarya

Hi LipChean,

Yes , that is exactly what I meant.

Thanks and regards,

Aishwarya

Hi @ben_speight ,

We have tried using NaN logic, but we are still not getting the desired result. The supporting screenshots are attached herewith.

Thanks and regards,

Aishwarya

NaN_Chart_42958.png

NaN_workout_42958.jpg

LipChean_Soh

Hi Aishwarya,

To create a NaN, you need to write Divide(0,0).

Inserting NaN in the module, the following graph is derived.

Screen Shot 2019-03-21 at 8.22.02 AM.png

I prefer having 0 in my graph, because it's reflecting the true value.

But if you're still looking for a graph that bypasses 0, and connects only values, i think there's 2 choices:

Thanks,

LipChean

Aishwarya

Hi LipChean,

Thanks a lot ! NaN creation using Divide function works for us. Our zeroes and either at start or at the end , hence this solves our purpose.

Thanks and regards,

Aishwarya

Quick Links

Can you help?

Unanswered questions

Request for Workflow Design: End-User Report Finalization & Stakeholder Notification with Version Co
Hi everyone, I'm looking to design a workflow and would appreciate your input or guidance on how to set this up effectively in Anaplan. So, the goal is: Once end users complete their data input, they should be able to trigger a process that: Notifies stakeholders via email with a link to a report. The report includes: A…
Generating Failure Dump - even though no warnings/error in Actions
I've few actions in our Supply model which when run takes some time on the "generating failure dump" step at the end of the action even though the import is a full success without warning or errors. This is just a few seconds when run for a single action, however we have a process with 120 actions, and most of these…
OAUTH 2.0 vs Google Sheets Extension
Hi, My org is looking to explore leveraging Google Apps script to extract data out of Anaplan and update already built templates in Google Sheets. We use the Anaplan Extension currently but find it crashes often with the amount of tabs, data, etc. Has anyone used both Anaplan OAUTH 2.0 API and Goolge Apps Script to write…