Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Line Chart Conditional Color/Shape Based on Time and Legend Breakdown?

Hi all, I'd like to do something that seems simple on the surface but is giving me a lot of grief.

 

I'm working with a set of data that contains revenue forecasts. These forecasts are quarterly and each forecast contains 8 quarters: 1 quarter of actual revenue (the "jump-off"), and 7 quarters of predicted revenue. This way I can also add a line that is solely for actuals data, and then have a bunch of lines for all of the forecasts and then I can see how the forecasts branch off of the actuals. These forecasts are created each quarter and have two "scenarios": a positive scenario and a negative scenario.

 

That's mostly fine, but I'm having trouble with the line colors/shapes in this chart. What I want is for each quarter, the Positive Scenario is a shade of blue and the Negative Scenario is shade of Red. I want the shades to be darker for more recent jump-offs, and lighter for older ones. The Legend is a key that combines the Scenario and Jump-off Name. Because I have a legend, there's no conditional formating available. Is there any way to have some kind of conditional logic behind the color/shape selection of the lines?

 

Here is the data:

Name Scenario KeyForecast NameScenarioStart DateRevenue DateRevenue
Q4'21: PositiveQ4'21Positive12/31/202112/31/2021110
Q4'21: PositiveQ4'21Positive12/31/20213/31/2022102
Q4'21: PositiveQ4'21Positive12/31/20216/30/2022104
Q4'21: PositiveQ4'21Positive12/31/20219/30/202298
Q4'21: PositiveQ4'21Positive12/31/202112/31/2022120
Q4'21: PositiveQ4'21Positive12/31/20213/31/2023118
Q4'21: PositiveQ4'21Positive12/31/20216/30/2023124
Q4'21: PositiveQ4'21Positive12/31/20219/30/2023125
Q4'21: NegativeQ4'21Negative12/31/202112/31/2021110
Q4'21: NegativeQ4'21Negative12/31/20213/31/2022108
Q4'21: NegativeQ4'21Negative12/31/20216/30/2022100
Q4'21: NegativeQ4'21Negative12/31/20219/30/202292
Q4'21: NegativeQ4'21Negative12/31/202112/31/202293
Q4'21: NegativeQ4'21Negative12/31/20213/31/202384
Q4'21: NegativeQ4'21Negative12/31/20216/30/202378
Q4'21: NegativeQ4'21Negative12/31/20219/30/202380
Q1'22: PositiveQ1'22Positive3/31/20223/31/2022100
Q1'22: PositiveQ1'22Positive3/31/20226/30/2022105
Q1'22: PositiveQ1'22Positive3/31/20229/30/2022110
Q1'22: PositiveQ1'22Positive3/31/202212/31/2022120
Q1'22: PositiveQ1'22Positive3/31/20223/31/2023119
Q1'22: PositiveQ1'22Positive3/31/20226/30/2023109
Q1'22: PositiveQ1'22Positive3/31/20229/30/2023121
Q1'22: PositiveQ1'22Positive3/31/202212/31/2023130
Q1'22: NegativeQ1'22Negative3/31/20223/31/2022100
Q1'22: NegativeQ1'22Negative3/31/20226/30/202290
Q1'22: NegativeQ1'22Negative3/31/20229/30/202287
Q1'22: NegativeQ1'22Negative3/31/202212/31/202275
Q1'22: NegativeQ1'22Negative3/31/20223/31/202379
Q1'22: NegativeQ1'22Negative3/31/20226/30/202384
Q1'22: NegativeQ1'22Negative3/31/20229/30/202376
Q1'22: NegativeQ1'22Negative3/31/202212/31/202374
Q2'22: PositiveQ2'22Positive6/30/20226/30/202295
Q2'22: PositiveQ2'22Positive6/30/20229/30/2022100
Q2'22: PositiveQ2'22Positive6/30/202212/31/2022124
Q2'22: PositiveQ2'22Positive6/30/20223/31/2023121
Q2'22: PositiveQ2'22Positive6/30/20226/30/2023104
Q2'22: PositiveQ2'22Positive6/30/20229/30/2023108
Q2'22: PositiveQ2'22Positive6/30/202212/31/2023115
Q2'22: PositiveQ2'22Positive6/30/20223/31/2024120
Q2'22: NegativeQ2'22Negative6/30/20226/30/202295
Q2'22: NegativeQ2'22Negative6/30/20229/30/202290
Q2'22: NegativeQ2'22Negative6/30/202212/31/202291
Q2'22: NegativeQ2'22Negative6/30/20223/31/202384
Q2'22: NegativeQ2'22Negative6/30/20226/30/202373
Q2'22: NegativeQ2'22Negative6/30/20229/30/202367
Q2'22: NegativeQ2'22Negative6/30/202212/31/202369
Q2'22: NegativeQ2'22Negative6/30/20223/31/202472
ActualActualActual 12/31/2021110
ActualActualActual 3/31/2022100
ActualActualActual 6/30/202295

 

 

Here's what I have now (I have manually set the colors):

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I have an issue when I select a sub-section of forecasts. The colors aren't dynamic so if I select Q4'21 and Q1'22, I would like Q1'22 to be the darkest possible shade of red and blue, but it doesn't do that. It displays as the color that I had hardcoded it to be:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Is there any way to have conditional color and shape formatting when I have a legend? I am also planning on using a linked table (created using CROSSJOIN) to have a conditional legend for this table, so that I can split the legend by either "Name Scenario Key" or "Forecast Name" - That will allow me to see time series of total or it's sub-parts. Not sure if that will affect the logic. 

 

I would appreciate any help on this! As far as I know, there's no way to have conditional line colors, but hopefully I'm wrong. Thank you!

 

 

 

Need Help

1 Reply