How to change colors of the chart based on timeframe

gbrown

I am trying to change the colors of current month and future months a different color than past months. Past months show actual values and current/future months show forecast values. How would I go about doing this? What I would like is past months to be the current color below and current/future months to be a lighter color to be able to visually see the difference between actual and forecast values.

MarkSnodgrass

You can do this by creating beast modes for each of your series for current/future months and another for historical months. Since you have 3 bars, that would indicate you have 3 items in your series, so you would need to create 6 beast modes. Each would look like this:

Series A Historical Beast Mode

CASE WHEN seriesname = 'A' AND LAST_DAY(dt) < LAST_DAY(CURRENT_DATE()) THEN value END

Series A Current/Future Beast Mode

CASE WHEN seriesname = 'A' AND LAST_DAY(dt) >= LAST_DAY(CURRENT_DATE()) THEN value END

Series B Historical Beast Mode

CASE WHEN seriesname = 'B' AND LAST_DAY(dt) < LAST_DAY(CURRENT_DATE()) THEN value END

Series B Current/Future Beast Mode

CASE WHEN seriesname = 'B' AND LAST_DAY(dt) >= LAST_DAY(CURRENT_DATE()) THEN value END

Series C Historical Beast Mode

CASE WHEN seriesname = 'C' AND LAST_DAY(dt) < LAST_DAY(CURRENT_DATE()) THEN value END

Series C Current/Future Beast Mode

CASE WHEN seriesname = 'C' AND LAST_DAY(dt) >= LAST_DAY(CURRENT_DATE()) THEN value END

Drag all of these int your Series.

Now that they are all individual fields, you can use Color Rules to assign specific colors to each (i.e. Green and Light Green for the 2 Series A items, etc.)

DavidChurchman

So right now you have 4 groups in your series, let's say they're called:

• Line A
• Bar A
• Bar B
• Bar C

Could create a single BeastMode that appends something like "projected" to the end of each of those.

CASE WHEN LAST_DAY(date) >= LAST_DAY(CURRENT_DATE()) THEN CONCAT(seriesname, " projected")

else seriesname

END

That should split your series into eight groups:

• Line A
• Bar A
• Bar B
• Bar C
• Line A projected
• Bar A projected
• Bar B projected
• Bar C projected

And you should be able to adjust the color rules on each of those.

You will also want to change the number of series for a line from the default to 2, so that both "Line A" and "Line A projected" are graphed as a line. The other challenge there is that you will need to make sure your sort puts your two groups in the series first. Something like this:

• Line A
• Line A projected
• Bar A
• Bar B
• Bar C
• Bar A projected
• Bar B projected
• Bar C projected

Here's what it could look like:

The upside of this approach compared to Mark's is one BeastMode vs. 8. (TBH, I really used two, because I used a BeastMode to help the sort. Mark's approach is easier to control the bar order, since you're dragging the different BeastModes in).

A downside of of both of our approaches is that there is a discontinuity between actual and projected, and it creates empty slots in the grouped bar (there are three empty bars on the right side of the actuals and three empty bars on the left side of the projected).

MarkSnodgrass

You can do this by creating beast modes for each of your series for current/future months and another for historical months. Since you have 3 bars, that would indicate you have 3 items in your series, so you would need to create 6 beast modes. Each would look like this:

Series A Historical Beast Mode

CASE WHEN seriesname = 'A' AND LAST_DAY(dt) < LAST_DAY(CURRENT_DATE()) THEN value END

Series A Current/Future Beast Mode

CASE WHEN seriesname = 'A' AND LAST_DAY(dt) >= LAST_DAY(CURRENT_DATE()) THEN value END

Series B Historical Beast Mode

CASE WHEN seriesname = 'B' AND LAST_DAY(dt) < LAST_DAY(CURRENT_DATE()) THEN value END

Series B Current/Future Beast Mode

CASE WHEN seriesname = 'B' AND LAST_DAY(dt) >= LAST_DAY(CURRENT_DATE()) THEN value END

Series C Historical Beast Mode

CASE WHEN seriesname = 'C' AND LAST_DAY(dt) < LAST_DAY(CURRENT_DATE()) THEN value END

Series C Current/Future Beast Mode

CASE WHEN seriesname = 'C' AND LAST_DAY(dt) >= LAST_DAY(CURRENT_DATE()) THEN value END

Drag all of these int your Series.

Now that they are all individual fields, you can use Color Rules to assign specific colors to each (i.e. Green and Light Green for the 2 Series A items, etc.)

DavidChurchman

So right now you have 4 groups in your series, let's say they're called:

• Line A
• Bar A
• Bar B
• Bar C

Could create a single BeastMode that appends something like "projected" to the end of each of those.

CASE WHEN LAST_DAY(date) >= LAST_DAY(CURRENT_DATE()) THEN CONCAT(seriesname, " projected")

else seriesname

END

That should split your series into eight groups:

• Line A
• Bar A
• Bar B
• Bar C
• Line A projected
• Bar A projected
• Bar B projected
• Bar C projected

And you should be able to adjust the color rules on each of those.

You will also want to change the number of series for a line from the default to 2, so that both "Line A" and "Line A projected" are graphed as a line. The other challenge there is that you will need to make sure your sort puts your two groups in the series first. Something like this:

• Line A
• Line A projected
• Bar A
• Bar B
• Bar C
• Bar A projected
• Bar B projected
• Bar C projected

Here's what it could look like:

The upside of this approach compared to Mark's is one BeastMode vs. 8. (TBH, I really used two, because I used a BeastMode to help the sort. Mark's approach is easier to control the bar order, since you're dragging the different BeastModes in).

A downside of of both of our approaches is that there is a discontinuity between actual and projected, and it creates empty slots in the grouped bar (there are three empty bars on the right side of the actuals and three empty bars on the left side of the projected).