Colour Coding data points (scatter plot) based on status.

liv.hagen

How can I display the priority rating of a task against the created date with the datapoint being colour coded by status. ie. in progress (green), on hold (red), backlog (black). I am pulling the data from this report, and have made a counter to display "1" if the task is in a certain status so that the chart has numerical values to pull from. However, I am not getting the visual I am looking for. I can get a visual of the priority against the created date, if anyone has any advice on how to get the datapoints colour coded it would be appreciated. Thanks!

Genevieve P.

Hi @liv.hagen

I would personally adjust the formulas you have in the 3 counting columns to instead show the value in the Priority Ranking column if it matches the Status that column represents:

=IF(Status@row = "Backlog", [Priority Ranking]@row, "-")

=IF(Status@row = "In Progress", [Priority Ranking]@row, "-")

=IF(Status@row = "On Hold", [Priority Ranking]@row, "-")

Then you'll have three columns that can be used in your Scatter Chart, so that it can map different colours to the different columns. Note that you'll want to de-select the "Priority Ranking" column from the chart so you don't have duplicate data:

Cheers,

Genevieve

liv.hagen

Thanks so much!

Masmuha

Hello, I'm wondering what account plan to have in order to avail of this feature. I have tried but it doesn't seem to work on my side. Do you need to have the business plan or will the pro plan work?

Thanks.

Genevieve P.

Hi @Masmuha

Pro Plans have access to Chart Widgets, including the Scatter Chart.

Can you show a screen capture of what you see when you're looking to add a Scatter Chart to your Dashboard?

Masmuha

I can add the scatter chart, but unfortunately it doesn't allow me to colour code them. I've followed your instructions as shown above and used the exact same method. Please see screenshot below! Thanks.

Genevieve P.

Hey @Masmuha

What does your source data look like? I can see in the legend of your chart that there's only one value: "Standard". Since it's only mapping one value, you can only have the one colour. Does that make sense?

Zach_

@Genevieve P. Thank you for your help here. It got me a lot closer to my solution than I was.

I'm still have issues, though. I can't figure out how to get a column to function as the X-axis, like you did with the "Created" column.

Below is a screenshot of my current result.

The red, light blue, and red dots represent my projects, which all have an impact score. Yellow ones are unstarted, light blue is in progress, and red are completed. The dark blue dots are the hours that are associated with each project. I don't want any dots representing the hours; instead, I want the X-axis to display a range. Where am I going wrong?

Genevieve P.

Hi @Zach_

Make sure your "Hours" column is the first column in the range. Then you can select the chart configuration option that says "Use first column as series labels" which will make it the X-Axis. 🙂

Zach_

Hi again @Genevieve P., that got me closer to the result that I want. Thank you for that.

One of the problems, still remains, though. There are two points representing each row on my sheet. There is the desired point, located at the appropriate intersection of the X- and Y-axes; this represents the cell that contains the impact score (to indicate the status). But there is also the undesired point, located directly on the X-axis; this represents the two status cells that only contain "-".

Having multiple points for each row makes the chart confusing. Is it possible to eliminate the points that represent the two empty cells? Your response to Liv makes it seem like you managed to do this, but I can't figure out how.

Genevieve P.

Hi @Zach_

In the example above, the X-Axis is using Dates. This then ignores any cells with text. However with a numerical X-Axis, it sees your text cells as "0", so it's mapping them at the bottom.

There currently isn't a way to remove 0s from Scatter Charts at the moment, but please add your vote to this related Product Idea: remove zeros from Scatter Charts

The alternative is to have 1 columns for Hours and 1 column for all Impacts, regardless of Status. This means you won't have colour coding for the different statuses, but it would remove any text/blank 0 cells.

Zach_

@Genevieve P. Bummer. I was hoping that wouldn't be the case. Thank you for the response, though. I voted on the idea.