Anyone able to help on this?
To make it easier to understand, I am looking for as dimensions, the sum of the top 25%, the sum of the average and the sum of the bottom 25% of my measure to be able to use in line with my selected customer, I am thinking that the three dimensions (top, average & bottom) would be best as sets.
I've managed to get my deadline extended to accommodate this; those posts are part the way there, what I have so far is two sets - Top 25 and bottom 25, which I am still trying to work out how to get them to be the top 25% of my measure and bottom 25% of my measure.
I have created four stacked bar charts: selectable (by single selection), sum of Top 25 using the set as the filter, the same for the bottom and then something similar for the average then moved them as close as possible on the dashboard.
I am finding that trying to place them together on the one chart messes up the partitioning and kills the charts.
So if you know of a way around this that shall allow me to achieve this I shall be most grateful.
I've attached both what I have so far, and what I am trying to achieve as the Excel piece I put together as the poc this one in particular needs to look like that of the orange tabs though I shall soon need to achieve the that of the purple tabs which I think your links my be able to achieve.
1 of 1 people found this helpful
I've had limited time to go at this and I'm not totally happy with the results, maybe Joe Mako can jump in.
I don't think Sets are the answer to create separate worksheets because you need to generate a measure (I wasn't clear on what that was, I created a % Satisfied measure), then you need to filter for the top 25%/bottom 25%/middle rows based on that measure. I came up with a couple of options:
The first option was based on the "top and bottom calc show 2nd calc jm edit" attached workbook from an old forum post that Joe and I worked on several months back - unfortunately, I can't find the original post, through the Tableau forum search or Google.
The "top and bottom 25 percent" workbook uses your initial workbook with a duplicated data source (so the calculated fields wouldn't get too confusing) and a set of table calculations to identify the top/bottom/middle, and to create measures for the % Satisfied/Disatisfied/Neutral in each, so you can end up with all three stacked bars in one graph. The problem here is that it takes 3+ minutes to refresh the view on my machine. There are definitely some optimizations that can happen, and I was running into a strange Tableau error that I might be finally able to replicate where adding a table calculation to the view was changing the results of other calculations. In any case, I ran out of time to make this work. You can see the two worksheets in Table Calc Workout and Table Calc View. Even if optimizations are done, it still might not be fast enough to be useful.
The alternative is along the lines of what you were creating, where there is one bar per top/bottom/middle. I used some of the calcs from above to do that for the top 25%, you can see that in the Single Chart sheet. You'd need to duplicate the calculations and adjust the filters to create additional worksheets to include in the final view.
In any case, I imagine that you'll be better off keeping the chosen field as a separate worksheet. That way when a user refreshes the dashboard only that worksheet needs to change, and not the totals worksheet(s).
Finally, the performance issues would entirely go away if you pre-calculated the initial measure (i.e. % Satisfied) and the position/percentile in SQL. I see you are using an extract, so the extra computation time while creating the extract shouldn't be an issue.
Also, I used a different color scheme than the one in the Excel wookbook, which used red/green/blue. Since ~10% of males are color-blind, it's not good practice to use red and green in the same view with no other differentiating factors like shape/angle/size.
Let me know if you have any questions,
Cripes! I am rustier than I first thought.
This is a brilliant effort - it still takes around 2.5 mins on my Core i7 with 8GB so going back to your previous point that breaking them down to 4 different charts may just be the way forward.
I do agree that the separate charts idea is far better performance wise, I provided just 10% of 1 months data for uploading, we are seeing circa 900k rows per month the only benefit being that Tableau was my choice so I now need to deliver in order to convince that Tableau Server would also be a good idea.
I would really like to see what Joe, Richard or Andy could come up with though I really do doubt that your efforts could be topped
On a side note I must admit, despite using window calcs regularly at server level, I've always avoided them in Tableau simply as I've never really understood how they work despite taking both the fundamentals & advanced courses last year [just never got it I suppose...]; my alternative method involves creating new connections to handle the top and bottom 25%, only this would likely slow everything down especially as I have an initial 5 measures to have to handle now [images, floorplans, virtual tours, two others I cannot remember atm...],; my method which does get the top/bottom 25% is:
With toprank As (
,Dense_Rank() Over(Order By images Desc) As rank_of_images
From mediabenchmark m
Where rank_of_images <= (
Select Round((Count(*)/4)*1,0) From toprank
With bottomrank As (
,Dense_Rank() Over(Order By images) As rank_of_images
From mediabenchmark m
Select * From
(Select * From bottomrank Order By RowNum Desc) bottomrank
Where Rownum <= (
Select Round((Count(*)/4)*1,0) From bottomrank
Order By RowNum Desc;
Attached is my first pass. I am not sure if I understand the exact computation that you want, but I am sure anything that you want can be achieved.
You can get all four stacked bars in a single sheet with a refresh rate of less than 10 seconds.
It uses a few tricks, so it is likely not an intutive approach.
You are welcome to contact me via the email in my profile to set a screen sharing session if you want to dive into this approach, or adjust it to fit your exact needs.
Apologies for the lateness, been away with whooping-cough...
I said it in my previous post that I didn't think either you Richard or Andy could better Jon, but I think you may have just improved on it especially in just one pass...
I shall take some time this evening to work out how you did it, but many thanks Joe anyway.