# VIZSCAPES

## July 6, 2018

## June 1, 2018

### Conditional Color Based on First / Second Column

###

###
This post describes how to make the conditional color based on either the first or second column which will set the colors for all other columns in the same row. To achieve this result, we’ll need to use the PREVIOUS_VALUE and LOOKUP functions in a calculated field.

###

###
In the Original worksheet,the conditional color for all years is red for profit ratio < 0%, orange for between 0% and 25%, and blue for > 25%.

###

###
Requirement: for all states except for Colorado, the conditional color for the first year 2013 should set the color for all subsequent years. But for Colorado, the conditional color for the second year 2014 should set the color for all other years.

###

###
In the Solution worksheet, the conditional color is set by this calculated field:

###

###
// For Colorado, conditional color is set by 2014 which is the second column.

###
IF attr([State])='Colorado' and attr(year([Order Date])) = 2014 THEN

###
(if [Profit Ratio] < 0 then '< 0%'

###
ELSEIF [Profit Ratio] >= 0 and [Profit Ratio] < 0.25 then '0% to 25%'

###
ELSE '> 25%'

###
END)

###

###

###
// Since conditional color for Colorado is set by 2014 in the second column, then for 2013 use LOOKUP function to take profit ratio // for 2014 to set the conditional color.

###
ELSEIF attr([State])='Colorado' and attr(year([Order Date])) = 2013 THEN (

###
IF LOOKUP([Profit Ratio],1) < 0 then '< 0%'

###
ELSEIF LOOKUP([Profit Ratio],1) >= 0 and LOOKUP([Profit Ratio],1) < 0.25 then '0% to 25%'

###
ELSE '> 25%'

###
END)

###

###
// For all other states except Colorado, conditional color is set by 2013 which is the first column.

###
ELSEIF (attr([State])<> 'Colorado' AND ATTR(year([Order Date])) = 2013) THEN

###
(if [Profit Ratio] < 0 then '< 0%'

###
ELSEIF [Profit Ratio] >= 0 and [Profit Ratio] < 0.25 then '0% to 25%'

###
ELSE '> 25%'

###
END)

###

###
// Use previous value to set conditional color for subsequent years.

###
ELSE PREVIOUS_VALUE(" ")

###
END

###

###
So for Alabama, profit ratio for 2013 is 20% which should be orange, so all subsequent years are orange. For Arizona, profit ratio for 2013 is -11% which should be red, so all subsequent years are red, and so forth. But for Colorado, profit ratio for 2014 is -25% which should be red, so all other years are red as well.

### This post describes how to make the conditional color based on either the first or second column which will set the colors for all other columns in the same row. To achieve this result, we’ll need to use the PREVIOUS_VALUE and LOOKUP functions in a calculated field.

###
In the Original worksheet,the conditional color for all years is red for profit ratio < 0%, orange for between 0% and 25%, and blue for > 25%.

### Requirement: for all states except for Colorado, the conditional color for the first year 2013 should set the color for all subsequent years. But for Colorado, the conditional color for the second year 2014 should set the color for all other years.

###
In the Solution worksheet, the conditional color is set by this calculated field:

### // For Colorado, conditional color is set by 2014 which is the second column.

### IF attr([State])='Colorado' and attr(year([Order Date])) = 2014 THEN

### (if [Profit Ratio] < 0 then '< 0%'

### ELSEIF [Profit Ratio] >= 0 and [Profit Ratio] < 0.25 then '0% to 25%'

### ELSE '> 25%'

### END)

### // Since conditional color for Colorado is set by 2014 in the second column, then for 2013 use LOOKUP function to take profit ratio // for 2014 to set the conditional color.

### ELSEIF attr([State])='Colorado' and attr(year([Order Date])) = 2013 THEN (

### IF LOOKUP([Profit Ratio],1) < 0 then '< 0%'

### ELSEIF LOOKUP([Profit Ratio],1) >= 0 and LOOKUP([Profit Ratio],1) < 0.25 then '0% to 25%'

### ELSE '> 25%'

###
END)

### // For all other states except Colorado, conditional color is set by 2013 which is the first column.

### ELSEIF (attr([State])<> 'Colorado' AND ATTR(year([Order Date])) = 2013) THEN

### (if [Profit Ratio] < 0 then '< 0%'

### ELSEIF [Profit Ratio] >= 0 and [Profit Ratio] < 0.25 then '0% to 25%'

### ELSE '> 25%'

###
END)

### // Use previous value to set conditional color for subsequent years.

### ELSE PREVIOUS_VALUE(" ")

### END

### So for Alabama, profit ratio for 2013 is 20% which should be orange, so all subsequent years are orange. For Arizona, profit ratio for 2013 is -11% which should be red, so all subsequent years are red, and so forth. But for Colorado, profit ratio for 2014 is -25% which should be red, so all other years are red as well.

## December 22, 2017

### Show Sales Lines Based on Profit Condition of Latest Month

###
This post describes a solution on how to show sales lines
by category that meet *minimum profit
condition based on latest month*.
The problem is interesting in that you need to combine multiple
Tableau concepts (Level of Details and Order of Operation) in order to get to
the solution. Many thanks to Shinichiro
Murakami for his helpful contribution to find the solution.

### The solution has to meet following two conditions (1) Minimum Profit Level and (2) Latest month.

###

First, create a calculated field to filter for the latest
month.

### Second, create another calculated field to show profit based on latest month.

### Third, create another calculated field to filter for latest month profit that meets minimum profit level.

### Since we want to show the sales line over a range of Order Date, this date range should be filtered first before the calculation for @Latest Month. So add the MONTH(Order Date) filter to context which will make this filter have a higher order of operation.

###

Finally, since we want to show only sales lines that meet
minimum profit condition on latest month, drag @Profit Condition Filter to
Filters pane, change to Exclude Values and select Null. This will exclude sales lines for categories
that don’t meet profit condition and hide those sales lines.

Subscribe to:
Posts (Atom)