11 Replies Latest reply on Oct 30, 2018 8:39 AM by Ken Flerlage

# Slope color change

Hello,

I was trying to use this example but seem to be stuck. I would like to filter by region and 2 ore more years with the same green, grey, red color.

Cheers,

-Joe

• ###### 1. Re: Slope color change

You have a number of different problems here. First of all, you're trying to show the change between the two years that you've filtered, but then using an LOD. If you look at Tableau's Order of Operations, you'll see that dimension filters are calculated after FIXED LOD calculations, so your LOD is actually getting the MAX and MIN years in the entire data set, not the years you've selected. To correct that, you should add that filter to context. Context filters are calculated before FIXED LOD calculations. In the same light, you should also add your region filter to context.

A second problem is that a lot of the countries' 2016 data has zeros so you can't really calculate a change from year to year because you'll get a divide by zero problem. To avoid that issue, I'm looking at 2015 vs 2017.

To simplify some of the calculations, I've broken out your Calculation1 into 3 separate calcs:

Fav - First Year

// SUM of Fav for the first selected year.

{ FIXED [Country]:SUM(IF [Year]= {FIXED [Country]:MIN([Year])} THEN

[Fav] END)

}

Fav - Last Year

// SUM of Fav for the last selected year.

{ FIXED [Country]:SUM(IF [Year]= {FIXED [Country]:MAX([Year])} THEN

[Fav] END)

}

Change

// Change from first year to last year.

([Fav - Last Year]-[Fav - First Year])/[Fav - First Year]

Finally, we'll do one more calculation to find which country has the min and max change:

Change Category

// Identify the countries with the min/max amount of change.

IF [Change]={FIXED : MAX([Change])} THEN

"Max"

ELSEIF [Change]={FIXED : MIN([Change])} THEN

"Min"

ELSE

"Other"

END

Drag Change Category to the color card, then change the colors as desired.

See attached.

• ###### 2. Re: Slope color change

Thank you for the order of operations reminder, I see why it wasn't working.

• ###### 3. Re: Slope color change

Any time!

• ###### 4. Re: Slope color change

Hi Ken,

Just curious to know on your marks shelf;

1. Why do you have Country on detail twice?
2. Why do you have the Max Fixed expression on detail?

Also do you have any suggetions on how to filter out countires with no value?

• ###### 5. Re: Slope color change

Neither are needed. I was just testing the values there. I meant to remove them.

If you'd like to filter out countries with no value in the first year, then you can just filter on the Fav - First Year field.

• ###### 6. Re: Slope color change

I've add First and Last years to the filter, do they need to be added to context as well?

• ###### 7. Re: Slope color change

You don't need to add it to context. It's based on an LOD, so we want to first calculate the LOD, then apply the filter. Setting up the filter something like the following ought to do it.

• ###### 8. Re: Slope color change

That's how I set it up, but if I only use the first year as a filter it inclues those without a value in the last year (filter_red).

If I include the last year, it dosen't show the red decline (filter_no_red).

• ###### 9. Re: Slope color change

I was able to get the filter working, thanks again for your help!

• ###### 10. Re: Slope color change

Hi Ken,

I've got the filter applied using 4 decmials, any idea why Lebanon shows up for 2006?

• ###### 11. Re: Slope color change

I think it's because you've added those 2 filters to context. If you remove them from context, it should work.

1 of 1 people found this helpful