7 Replies Latest reply on Aug 22, 2017 8:09 AM by Brad Sheridan

# Custom trend line calculation used for color?

Hey there Community!  I've got a question that has stumped me for the past day (it's possible that this can't even be accomplished)

I've got the dashboard tab shown below.  Obviously when a bar is selected at the top, the bottom line chart renders for the 'company' only.  As you can see, I added a trend line to the bottom chart.

What I'd now like to do is:

1) somehow capture the trend line formula in a calculated field

2) use that calculation to color the bars on top.

Basically I want the bars colored to indicate where the slope is declining/negative vs. increasing/positive

thoughts?

• ###### 1. Re: Custom trend line calculation used for color?

Without knowing much details about your data, my first intuition is that you can create a set using formula to separate increase and decrease. Then use this set as one of your filters and apply it into the chart with color formatting and other functions you want.

• ###### 2. Re: Custom trend line calculation used for color?

Basically you want to see if the values for your company increased or decreased between jan 15 to Jul 2nd. And based on that you want to color the company bars.

here is what i did.

I calculated the first year sales and last year sales using the below formulas. I used superstore data for sample.

IF YEAR([Order Date]) = YEAR({ FIXED : MIN([Order Date])})

THEN

[Sales]

END

IF YEAR([Order Date]) = YEAR({ FIXED : MAX([Order Date])})

THEN

[Sales]

END

Then, I used the below formula to check if the sales increased by the last year.

(SUM([last year sales ]) - SUM([first year sales])) > 0

I dropped the above calculated fileds to color the bars. I am attaching the tableau file for reference.

• ###### 3. Re: Custom trend line calculation used for color?

Thanks Kalyan and @yvonne guo.

Both are great solutions to other tasks, but not necessarily what I'm trying to accomplish.  Let me expand on my original post a bit...

When I mouse over the trend line in my original screenshot, I get the tooltip shown below.  You can see from the slope of the line and from the formula, that this line has a positive slope.  What I'd like to do is capture this calculation somehow and when the value is positive, color the bar on the chart at the top (refer to original post image) one color, and if the result of the formula is negative (a downward sloping line), color the bar a different color.

I think the issue is that I don't have the required fields on the bar chart sheet

Clear as mud?

• ###### 4. Re: Custom trend line calculation used for color?

If you are using linear regression, all data would only have one slope. in this case you wouldn't need an automatic way to change the color formatting on those bars.

In case you still want to make it happen, one option is to create a calculated field as slope manually.

({fixed:count([X])}*{fixed:sum([Y]*[floatdate])} - {fixed:sum([floatdate])}*{fixed:sum([Y])}) /

({fixed:count([X])}*{fixed:sum(power([floatdate],2))}-power({fixed:sum([floatdate])},2))

this is an example of linear regression

hope it helps.

1 of 1 people found this helpful
• ###### 5. Re: Custom trend line calculation used for color?

Thanks Yvonne.  Unfortunately the bar chart sheet in the top half of the dashboard doesn't have to same fields (week, etc...) as the line chart at the bottom (the one with the trend line).  Looks like I'll have to come up with another solution

thanks