Hi,
I need help creating a date filter that would show the variance between 2 years, depending on the year the user picks. Sorry if that's confusing.
Ex. When user picks 2019 from the filter, table should display the variance between 2019 and 2018. If they pick 2018, then show the variance between 2018-2017
The problem I'm having is I can't seem to find a way to show the data for a single year. Like I have to have 2 of the years selected in order to get the variance data.
I have no idea how to create a separate data variance column. Right now, I'm using a table calculation on the SUM value to do the variance.
I have attached a Tableau workbook in the question. I'm using the sample superstore data. Also, the year I am using in the sample is a DATE, but in my actual data it's a STRING variable and it cannot be changed. Please help me.
My recommendation would be to use LOD calculations to isolate the given year. Start by creating a parameter for the year.
Then create your LOD calculated fields--one for the selected year, one for the previous, and one for the variance.
Profit - Current
// Profit for the current year.
{FIXED [Category]: SUM(
IF YEAR([Order Date])=[Select Year] THEN
[Profit]
END
)}
Profit - Previous
// Profit for the previous year.
{FIXED [Category]: SUM(
IF YEAR([Order Date])=[Select Year]-1 THEN
[Profit]
END
)}
Variance
// Profit variance current to previous.
[Profit - Current]-[Profit - Previous]
See attached.
