CODEWITHSUNDEEP
tableau-api
5E4ME
5E4ME

Reputation: 273

Tableau - Change axis display values without altering underlying data

I am doing a year over year comparison of two different products' sales performance. The data is pulled from a single SQL server table and is split into current and prior year by filtering on the following calculated field:

IF year([Friday of Week Sold]) = YEAR(TODAY())-1 THEN 
    IF [Product Type] = "A" THEN "PY Product A"
    ELSEIF [Product Type] = "B" THEN "PY Product B"
    END
ELSEIF year([Friday of Week Sold]) = YEAR(TODAY()) THEN 
    IF [Product Type] = "A" THEN "CY Product A"
    ELSEIF [Product Type] = "B" THEN "CY Product B"
    END
END

(calculated field inspired by blogpost here)

The end result is the chart below:

YoY chart

what I'm trying to figure out is if there is a way to change the display of the x-axis so that it lists the current year week-end dates rather than the week number; e.g. display "Week 1" as "1/4/19", "Week 2" as "1/11/19", etc. Changing the datatype to week doesn't work as it then splits the data in two like so: enter image description here and I'm trying to keep the lines overlaid on each other to make it easy for users to compare.

I am OK with the minor variance in dates from this (Week 1 in 2019 ends 1/4/19 vs Week 1 of 2018 ending 1/5/18). The goal is to enable end users to make a quick, rough comparison of our performance this week vs last year.

Upvotes: 1

Views: 399

Answers (1)

MonteCarloSims
MonteCarloSims

Reputation: 1771

Its not the most elegant solution, but it works: 

if DATEPART('week',[Order Date],'Friday') = 1 Then #1/4/2019# 
ELSEIF DATEPART('week',[Order Date],'Friday') = 2 Then #1/11/2019#
ELSEIF DATEPART('week',[Order Date],'Friday') = 3 Then #1/18/2019# 
ELSEIF DATEPART('week',[Order Date],'Friday') = 4 Then #1/25/2019#
ELSEIF DATEPART('week',[Order Date],'Friday') = 5 Then #2/1/2019#
ELSEIF DATEPART('week',[Order Date],'Friday') = 6 Then #2/8/2019#
ELSEIF DATEPART('week',[Order Date],'Friday') = 7 Then #2/15/2019#
ELSEIF DATEPART('week',[Order Date],'Friday') = 8 Then #2/22/2019#
[...52...]
END

The downside here is that it will take adjustment every calendar year to bring the dates to the current year.

The final product will require you to put the YEAR() of the measure you would like to have as your lines onto color (in your case, probably the nested IF would be a separate calculation) (in the below example [Profit].) The result is that each week number gets rolled into the above calculation which is being used as the dimension. You'll need to change chart type to 'Line' and you'll probably also want to right click the axis > rotate label.

enter image description here

Upvotes: 1

Related Questions