11 Replies Latest reply on May 30, 2016 9:29 AM by Ramesh Krishnan

How do I plot data by weeks, but show axis in Months (discrete)?

Ramesh Krishnan May 24, 2016 3:14 AM

Hello Helpers,

I have a situation where I have weekly data across 3 years. I need to plot it on a chart where the x-axis has to be months (Jan, Feb, till Dec). In Tableau, if I choose the Date as months, then the data also gets plotted at a monthly level.

What I want is, the data should be plotted at a weekly level, but the axis should be in Months. It is possible in Excel. It should be like in the below screenshot.

What I already tried:

1. I changed the date pill to continuous. Then I am able to "Edit Axis" and get custom format of the axis. If I choose "mmm" in it, I get what I want. But then, because the date is continuous, I am not able to get the lines for each year one above the other. Instead, it becomes one continuous line. This is something that I do not want.

2. I also saw the option of creating a dummy worksheet with just the month names in column, and then combining the sheet with the actual chart and the dummy sheet with just the month names in a dashboard (using different layout containers). This is a hacky way and it does solve the problem. But then I need to scale it and this hack is not scalable.

It'll be great if someone could help me here.

Thanks a lot,

Ramesh.

1. Re: How do I plot data by weeks, but show axis in Months (discrete)?

John Sobczak May 24, 2016 3:35 AM (in response to Ramesh Krishnan)
You can use dual axis and don't show the header for the axis in weeks as in the attached.

Plot by Weeks Axis Label by Months.twbx 455.9 KB
Like Show 0 Likes(0)

Actions
2. Re: How do I plot data by weeks, but show axis in Months (discrete)?

Stephen Lavery May 24, 2016 3:35 AM (in response to Ramesh Krishnan)
Hi Ramesh,

Like John above I've gone for the dual axis method. I've had to make the trend lines by month transparent so you cant see them. Only issue with this method is that I have lots of 'extra' axis beacuse of the month value. This could be worked around by fixing the axis to a certain limit:

See the attached workbook.

Kind Regards,
Stephen

Sales by Week with Axis by Months.twbx 1.1 MB

2 of 2 people found this helpful
Like Show 3 Likes(3)

Actions
3. Re: How do I plot data by weeks, but show axis in Months (discrete)?

Pranay Sahu May 24, 2016 3:41 AM (in response to Stephen Lavery)

HI Ramesh,
Please try this, you can take month as discrete and format the axis which you are already doing.
and just use the year in colour mark.
Hope it will solve your query.
Like Show 0 Likes(0)

Actions
4. Re: How do I plot data by weeks, but show axis in Months (discrete)?

Ashish Chaudhari May 24, 2016 4:01 AM (in response to Pranay Sahu)

Hi Pranay,

I guess your data is at month level. I think trend needs to be by weeks and axis should display as months.

-Ashish
Like Show 0 Likes(0)

Actions
5. Re: How do I plot data by weeks, but show axis in Months (discrete)?

Ramesh Krishnan May 24, 2016 5:57 AM (in response to John Sobczak)

Hi John,
Thanks for the super-quick reply. You have taken the date as actual dates (what I mean by this is, your dates are like Jan 2011, Feb 2011, etc. and they move on to Jan 2012, Feb 2012, etc on the x-axis itself).
I wanted it to be Jan to Dec in the x-axis and the different years should be shown in different colors (the x-axis should not have any year information).
Stephen's answer is exactly what I was looking for.
Thanks again!

Ramesh
Like Show 0 Likes(0)

Actions
6. Re: How do I plot data by weeks, but show axis in Months (discrete)?

Ramesh Krishnan May 24, 2016 6:18 AM (in response to Stephen Lavery)

Hello Stephen,

This is exactly what I wanted to do. Thanks for helping the community here. I will mark this post as the correct answer (hope you do not mind) because I am listing down the steps for others to quickly execute.

I am listing down the steps right here.

1. Bring the month pill on to the column shelf, then expand it to week number (keep the data parts alone, I mean Month should be May, not May 2015, and week number should also be like that). By default, both will be marked as discrete (blue pills).
2. Bring your measure on to the row shelf.
3. Change both month and week to continuous (now the pills will turn green). Make sure Month pill stays on the left side of week pill.
4. Mark week as "dual axis".
5. On the marks card, choose the card for month and click on color and change transparency to 0%.
6. Click on week number pill and uncheck show header.
7. Because you marked the month pill as continuous, the axis will be like Dec, Jan, Feb, ... , Nov, Dec, Jan. But we really don't want the additional Dec and Jan. So, just right click the axis and "Edit axis" and choose fixed range and make it 0 to 12. Now your axis will have labels from Jan to Dec only.

There you go

Thanks, Stephen, for providing the correct answer!

Regards,
Ramesh
Like Show 1 Likes(1)

Actions
7. Re: How do I plot data by weeks, but show axis in Months (discrete)?

Stephen Lavery May 24, 2016 6:33 AM (in response to Ramesh Krishnan)

No problem! Glad I could help
Like Show 0 Likes(0)

Actions
8. Re: How do I plot data by weeks, but show axis in Months (discrete)?

Ramesh Krishnan May 30, 2016 1:17 AM (in response to Ramesh Krishnan)

Guys, a follow up question. I just noticed that this method removes the colors from the legends! Is there anyway to retain the color legends? Because, otherwise, the whole exercise is useless!

The legend vanishes the moment I change the transparency of Months to 0.

Notice that the years 2012, 2013, 2014 do not have color legends!!

Thanks,
Ramesh
Like Show 0 Likes(0)

Actions
9. Re: How do I plot data by weeks, but show axis in Months (discrete)?

Ramesh Krishnan May 30, 2016 1:17 AM (in response to Stephen Lavery)

Hi Stephen, could you please take a look at my follow-up question?
Like Show 0 Likes(0)

Actions
10. Re: How do I plot data by weeks, but show axis in Months (discrete)?

Stephen Lavery May 30, 2016 6:19 AM (in response to Ramesh Krishnan)
Hi Ramesh,

That's a fair point. What you can do then is create a copy of the dimension you want in colour and drop this into the Week axis. This version will not be transparent and then you can hide the old one:

Best,
Stephen

Edit:

Actually you can just remove Year from the MONTH(Order Date) line all together and then just have it in WEEK(Order Date). This is simpler than my above response:

Sales by Week with Axis by Months.twbx 1.1 MB

1 of 1 people found this helpful
Like Show 0 Likes(0)

Actions
11. Re: How do I plot data by weeks, but show axis in Months (discrete)?

Ramesh Krishnan May 30, 2016 9:29 AM (in response to Stephen Lavery)

Thanks, Stephen!
Like Show 0 Likes(0)

Actions