I'm trying to build a chart in Tableau that combines a side-by-side bar chart with a line chart.
Currently, my view shows Sales as side-by-side bars:
- Current Year Sales
- Previous Year Sales
I would like to add a line that represents the Year-over-Year (YoY) % difference for each month, calculated as:
(Current Year Sales - Previous Year Sales) / Previous Year Sales
For example:
- January: Compare January CY vs January PY and plot the YoY %
- February: Compare February CY vs February PY and plot the YoY %
- ...and so on for each month.
My goal is to build this as a Dual Axis chart, where:
- The bars represent Current Year and Previous Year Sales.
- The line represents the monthly YoY % difference.
However, I'm running into an issue when calculating the YoY %. The values displayed in the line chart don't seem to be correct, and I suspect it's related to the level of detail or the table calculation, but I haven't been able to figure it out.
Has anyone built a similar visualization before? Is there a recommended approach for calculating the monthly YoY % correctly in a dual-axis side-by-side bar chart? Any guidance or example workbook would be greatly appreciated.
#Tableau Desktop & Web Authoring #Tableau Cloud #Tableau Public
I'll reiterate both yours and @Gohar Clients thoughts on the compute by - definitely on the right track. Here's where I can take things by working on that and a small tweak and additional calc:
Workbook re-attached.
First thing you'll see is a new calc: YoY (Latest Year Only)
IF LAST()=0THEN [YoY]END
(NB: I also renamed your "Calculation 3" to YoY).
The new calc just says "only return a value for the last year* in the view". We do have to tell Tableau that we care about the year for this part of the calc. And what we end up with is a nested table calc where the compute by for one part is different to the other!
Right click and edit the table calculation pill on Rows:
First up I'm working on the "Latest Year Only" calc as you can see in the top drop down. And I tell it to compute by specific dimensions and year only. So the latest year should be the last (LAST()=0) in the window.
Now I change the drop down to the "YoY" calc and tell this to compute by specific dimensions again, but this time Year and Month (in that order)
NB: Show calculation assistance is quite handy here as I can check that I am indeed seeing values 13-24 in the window. Now you might be thinking "hang on, how did my lookup -1 work in that case?!?" and you'd be right! I also changed the lookup to be a lookup by -12 so now we're looking back to the month 12 months ago.
The lack of an ELSE in the second calc is what supresses the previous year from showing ... it's still available to the calc as we've "filtered" using a table calc so that happens late in the order of operations ... and those are the "nulls" shown by the >12 nulls indicator.
Finally I CTRL+click and drag the pill from Rows onto Mark Text too so that I don't need to repeat the nested compute by there.
Let me know if this helps!
Compute by is often fiendish enough without (a) nesting; and (b) your great workaround for side-by-side without a discreet pill on Columns!
Ta, Steve