3 Replies Latest reply on Jun 26, 2018 3:06 AM by Matthijs Blenkers

# Coloring Area Chart Red or Green Based on Month Parameter

I have two Cal cautions created which changes based on the year parameter selected.

1) Current year sales

sum(if YEAR([Order Date])=[Year] and

month([Order Date])<=[Month]

then [Sales] end)

2) Previous year sales

sum(if YEAR([Order Date])=[Year]-1

then [Sales] end)

I have a dual axis chart where line chart shows Previous year sales and Area chart shoes current year sales up to the latest month selected from Month Parameter.

I want to create a calculation which satisfy below logic

Logic: Area chart should be Colored Red if Current Year sales -Previous Year sales <0 else Green.

Eg:  When User selects 2016 and May in parameter. Then Current Year- Previous Year is

44261-56691=-12430(Which is less than Zero. Hence the area chart should be Red else Green)

Eg:  When User selects 2016 and Jun in parameter. Then Current Year- Previous Year is

52982-39430=+13552(Which is Greater than Zero. Hence the area chart should be Green else Red)

The Coloring should be dynamic based on the month selected.

Thank You

• ###### 1. Re: Coloring Area Chart Red or Green Based on Month Parameter

HI Youthesh,

if

{ EXCLUDE DATEPART('month', [Order Date]) : (

sum(if YEAR([Order Date])=[Year] and

month([Order Date])=[Month]

then [Sales] end)

-sum(if YEAR([Order Date])=[Year]-1

and month([Order Date])=[Month]

then [Sales] end) <=0)}

then 'Red'

else 'Green'

END

and place it onto colour.

Thanks,

Mavis

• ###### 2. Re: Coloring Area Chart Red or Green Based on Month Parameter

Hi

another way of achieving this is

Create two calculated fields. I just simplify the calculations using your already created fields and enclosed with LOD{}.

Current Month Sales:

{sum(if YEAR([Order Date])=[Year] and

month([Order Date])=[Month]

then [Sales] end)}

Previous Year Same Month Sales:

{sum(if YEAR([Order Date])=[Year]-1 AND MONTH([Order Date])=[Month]

then [Sales] end)}

color:

if [Current Month Sales]<[Previous Year Same Month Sales] then 'Red' ELSE'green'

END

Use this color field on your current year sales marks card.

See the screenshots

Hope this help.

Mahfooj

• ###### 3. Re: Coloring Area Chart Red or Green Based on Month Parameter

Hi,

I'm not sure what you're trying to show with the viz.

It looks like you want the color of the WHOLE area-chart to be determined by the comparison of a specific month (based on a parameter). But based on the lay-out of the viz Tableau has to show each month separate. Hence, the calculation and coloring is going bonkers :-)

You can do a couple of things

1. Use an area-chart in combination with a rolling total (so show YTD results based on parameter)

2. Use another viz like bar in bar to show the results and color each month

It all depends on what you want to tell with this viz.

gr.

Matthijs