How to Combine Multiple Date Levels in One Sheet

I was talking to a coworker about their project and they mentioned they needed to recreate an Excel table in Tableau. This is a pretty common ask but can be deceptively challenging because there are a number of manual formatting options available in Excel that don’t translate as easily to Tableau. This one looked like it wouldn’t be too much trouble but there was something I found interesting about it and wanted to give it a try as a challenge to myself. On this table, the dates were going across the top at the quarter level, unless the quarter wasn’t complete, then it was at the month level.

How to Combine Multiple Date Levels in One Sheet

Once I got started I realized it was simpler than I thought it was going to be. But I thought it was worth sharing anyway. I’ll be using Superstore data for this blog. You’ll need 4 calcs. 

Calc #1 and Calc #2 – Creating the 2 date fields for display 

Because this will be two date levels in one calc by the end, I wanted to format them in separate calcs first to display the way I want. 

At the quarter level: 

					console.log( 'Code "Q" + STR(DATEPART('quarter',[Order Date])) +" "+ RIGHT(STR(YEAR([Order Date])),2)is Poetry' );

At the month level:

					LEFT(STR(DATENAME('month',[Order Date])),3) +" "+ RIGHT(STR(YEAR([Order Date])),2)

Calc #3 – Count the number of months in a quarter

					{ FIXED [Quarter]: COUNTD(MONTH([Order Date]))}

Calc #4 – Return your formatted quarter label for quarters with 3 months, otherwise return your formatted month label

					IF [Months in Quarter] = 3 THEN [Quarter] ELSE [Month] END

Super simple, huh? While I haven’t run into this specific ask before, I could see the utility of reporting at a higher date level but still wanted to understand what was in flight. This can be easily accomplished using 2 sheets, but I wanted to do it in one to preserve scrolling and save time formatting. I hope this helps if you need to report the same information at two different levels of granularity.

Want to Learn More?

Read my other recent blog posts:

Dashboard Design Best Practices

How to Prototype

Why You Should Prototype

How to Integrate Pagination in Tableau: Part 1

Have any questions, comments, or curiosities? Feel free to reach out to me on Twitter at @AskTessellation or @Aabatani

About Tessellation

We are a modern analytics consultancy. We enable and manage organizations’ analytics and self-service teams by educating people, optimizing technology, developing world-class products, and providing sustainable results. Curious to know how we can level up your organization’s analytics? Click here!

Interested in joining the Tessellation Team as a data analyst, Tableau expert, dashboard designer, or data scientist? We’re hiring! Check out our latest job listings on our website and on our Linkedin page!

Subscribe to Our 'Data Cap' Newsletter!

Want a monthly insights from the Tessellation Team with tips, tricks, and secrets to improve your analytics?

Sign up below and we'll deliver articles, events, and how to's straight to your inbox.
Comments are closed.