10 Replies Latest reply on Jun 9, 2016 11:39 AM by Derrick Austin

# determine the colour of the whole line in a graph with a calc?

Hi everyone -

I really need some help here.

I have a graph that is linked to a filter.

based on whether the current spend is more or less than a year ago I want the line to be either green or red.

My current attempt at achieving this has failed -

I've tried various calcs but nothing is working for me. Any advice would be greatly appreciated.

Many thanks,

Ed.

• ###### 1. Re: determine the colour of the whole line in a graph with a calc?

Hey Edward,

Have you looked into LoD calculations?

It should go something like this:

Year of Dt

YEAR([MyDate])

Color of Line

IF

{FIXED [Year of Dt] : SUM(IF [Year of Dt] = {MAX([Year of Dt])} THEN [Spend] END)} >

{FIXED [Year of Dt] : SUM(IF [Year of Dt] = {MAX([Year of Dt] - 1)} THEN [Spend] END)}

THEN 'green'

ELSE 'red'

END

• ###### 2. Re: determine the colour of the whole line in a graph with a calc?

Hi Edward,

If I understand you correctly, you're asking to see the graph in GREEN if there has been activity in the last year, and RED if it has been over a year since you've seen activity?

If this is right, you can create a LOD calc that compares the most recent date per company to today.

IF TODAY()-{fixed: max([Date])} >364 THEN "OLD"

ELSE "RECENT"

END

Then you can use this calc to determine your color.

• ###### 3. Re: determine the colour of the whole line in a graph with a calc?

Hi Michael,

Actually what I am trying to do is see the graph green if spend in May 2016 is more than May 2015.

So in this example the whole line should be red.

• ###### 4. Re: determine the colour of the whole line in a graph with a calc?

Hi Derrick,

I don't have Tableau in front of me to try this out, but in my mind it makes sense. I'll see if it works tomorrow morning.

Many thanks,

Ed.

• ###### 5. Re: determine the colour of the whole line in a graph with a calc?

I tried your method Derrick however I think somewhere we need to tweak something:

In my data, "Month" is our date field, "Actual" is our spend field.

Here, although spend in May '16 is less than in May '15, the colour of line calc still returns "Red".

• ###### 6. Re: determine the colour of the whole line in a graph with a calc?

Ah, I misunderstood exactly what you were going for. We're close though. I've attached a sample workbook with SuperStore data. Basically, we will just want to switch it to look at the month level instead of the year.

MonthYear

IF MONTH([Order Date]) < 10 THEN

INT(STR(YEAR([Order Date])) + '0' + STR(MONTH([Order Date])))

ELSE

INT(STR(YEAR([Order Date])) + STR(MONTH([Order Date])))

END

Line Color

IF

{SUM(IF [MonthYear] = {MAX([MonthYear])} THEN [Sales] END)} >

{SUM(IF [MonthYear] = {MAX([MonthYear] - 100)} THEN [Sales] END)}

THEN 'green'

ELSE 'red'

END

Let me know if you have any questions on it!

1 of 1 people found this helpful
• ###### 7. Re: determine the colour of the whole line in a graph with a calc?

Hi Derrick,

Thanks for getting back to me. Unfortunately I'm unable to access the file (probably due to office security), but I have tried to replicate your suggested calcs:

[Month] is basically my "order date" field in the data set.

[Actual] is my "Sales" field in the data set.

In both occasions the Colour of Line calc returns "red":

Any advice you can offer would be a huge help.

Many thanks,

Ed.

• ###### 8. Re: determine the colour of the whole line in a graph with a calc?

Hey Ed,

On line 2 of the MonthYear calc, it should be a zero instead of an "o". (The forum formatting seems to make it slightly confusing...)

• ###### 9. Re: determine the colour of the whole line in a graph with a calc?

Hi Derrick,

I'm sure that we're close on this.

A couple of things:

Here are my Calcs:

I have changed the Line colour calc so  that the number is visible.

Notice here, that when I change the filter, the number from our "Line Colour" calc doesn't change.

Our we fixed at a level of detail that is too high?

• ###### 10. Re: determine the colour of the whole line in a graph with a calc?

Yes, this would bypass that. Try adding the "Directorate1" filter to Context. (Right click in the filters shelf -> Add to Context)