July 6, 2018

Adjust for Daylight Saving Time





Tableau has this Knowledge Base article on how to adjust a date field for Daylight Saving Time (DST).  However, after further testing, I found the formulas have some bugs.

 

Above are test scenarios for Daylight Saving Time adjustments in America and Europe.  The Datetime ‘OLD’ column used the (incorrect) calculated field from the KB article, whereas the Datetime ‘NEW & BETTER’ column used the (correct) calculated field below.  Notice the datetimes with red dot are the ones with discrepancies in the time adjustment.

 

Here’s the correct calculated field to adjust for DST in the USA:

 

 


 

 

 

 

And here’s the correct calculated field to adjust for DST in Europe:

 

 

 


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.



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.