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.
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.
Do you have more questions about Tableau? Talk to our expert consultants today and have all your questions answered!
