The index is wrong - we need to explicitly set it to QoQ, but can't because QoQ is a table calc. Still thinking....
I feel smarter already knowing that this problem will take a pro like you more than 2 minutes to figure out.
The one-click sort is a visual sort, so Tableau doesn't do a lot of recalculating. However, when you use a parameter, Tableau does more recalculating. I set up a parameter, then changed the index filter to INDEX()<=[Top N Param], and as far as I can see it works as desired now.
PS: The one downfall of this solution is that you can't dynamically set the parameter range, so you have to choose a number big enough for the data set.
Thanks Jonathan, but that didn't do it.
For example, when I open your workbook and sort by QoQ change in 2011 Q3, all is good, because the parameter control is wider than the full set of 17 sub cats. However, let's say I move the slider to top 13, and then sort QoQ change in 2012 Q4, Do that, then open up the slider to 17 or more and you'll see that the data below row 13 are not sorted.
We need a way to trick Tableau into doing work above and beyond the visual layer.
I very much appreciate your time and effort here.
Sorry about that, my brain wasn't working right on a Saturday morning. I was looking at Sales and not the QoQ change. The Sales will retain order when you sort, but the QoQ change does not…interesting inconsistency there, I'm imagining this is because the one-click sorts are still a fairly new feature and sorting by the results of table calcs is tricky.
I'll play around some more with this, right now I'm framing this as a situation where we need to sort by the result of a table calc (the QoQ change), then filter those sorted results (the Index filter). Alex, if you have any ideas, I'd love to see them!
I keep coming up blank - I would love to see a feature where we could create a table view, and then use this as another source. If the data changes, the new 'source' changes...
1 of 1 people found this helpful
Alex - that would be lovely!
John - I don't believe the quick sort will work in this case, there's no way I can see to make the sort extend through the data hidden by the Index filter when the quick sort is on a table calculation. I tried using a parameter to choose the Quarter, and a discrete calculated field to generate a QoQ number that can be used to force Tableau to sort the results. That works fine for sorting the results, however, the Index filter also needs to use that same sort and there's no easy way to do that because we'd need to address and partition on the results of a table calculation. I've been playing around with techniques for doing just that, but I'm not at a point yet where I can come up with a solution using that method.
I do have a theory about a workable alternative, though, and that's to use Custom SQL to pre-join the prior year's results so the calcs can stay aggregate calcs and not get into table calc craziness. If you need help, let me know and I'll see what I can do in the next day or so - I don't have time to put it together just now. Here's my idea, based not the Superstore Sales view you'd set up:
- Create an aggregate query that groups the Superstore Sales Orders table on quarter of Order Date (plus 1 year) and Product Sub-Category, also returning SUM([Sales]). This gets us the oQ of the QoQ, so let's call the measure oQ Sales.
- Use the original Superstore Sales query as the left side of a left join, and the aggregate query as the right side of the join. The join fields in Superstore Sales are quarter of Order Date and Product Sub-Category, to quarter of Order Date (plus 1) and Product Sub-Category from the aggregate query. We're using a left-join because some of the results of the join will be Null.
- In Tableau, create a QoQ Sales calc that is something like SUM([Sales])-ZN(ATTR([oQ Sales]))/ZN(ATTR([oQ Sales])). I'm using ATTR as a sanity check, since the oQ Sales is a pre-aggregated measure, I want to make sure that if I'm choosing the a finer or coarser level of detail in the view (like by having Product Name in the view, or just year of Order Date) that the QoQ Sales calc returns no results rather than incorrect results.
- Sort the Sub-Category by the QoQ Sales calc, Descending.
At this point, you shouldn't have to do anything with the Index field, there should be a one-click quick sort option available for QoQ sales, and the sort should extend through the hidden data just as it does for the Sum([Sales]) measure. Also, using this option you could use a regular filter on the quarter of Order Date and not a table calc filter, which would improve performance.
I got some one-on-one time with Joe Mako yesterday and he walked me through a solution quite similar to yours. Your zn part of the calc for delta is a great idea.
I will post it sometime this week.
Thanks for much for your thoughtful input.