4 Replies Latest reply on Feb 7, 2013 4:57 AM by James Egan

# How to sort into groups when using the "level of detail" field

We have a series of reports that require calculations at a deeper level of detail than the actual view requires.  We then take those calculations and create a field that analyzes the results and creates "buckets".  The buckets field goes into Color but it doesn't sort the data by that color.  We end up with the various colors spread throughout the bar.

I've attached a packaged workbook file with a summarized version of this issue.  If anyone can look at it and provide some guidance, we'd appreciate it.

Thanks.

• ###### 1. Re: How to sort into groups when using the "level of detail" field

Hi James,

The reason why you were seeing the red marks intermixing with the blue is an issue with sorting. You had two dimensions in the view, Month of Order Date and the Seller Key, with Seller Key being sorted by Revenue/Descending/Sum. This causes Seller Key to be sorted based on the overall revenue across all months, and that same sort order was being used in each month, and that order is used to determine where the marks are drawn.

This kind of view also draws a mark for every combination Seller Key & Month of Order Date that exists, so Tableau is drawing 1843 marks. This is inefficient and makes it harder to use tooltips, etc.

The alternative is to consider the goal, which is to have 12 marks (6 months * 2 for Top 20% and Remaining) each showing the sum of Revenue for those buckets, and then organize Tableau's output to meet that goal. In the "workout" worksheet in the attached I set up a crosstab showing the data. The view uses the following calculated fields:

Order Date + Seller Key

A string of Order Date + Seller Key, this will be used in the Compute Using of the table calcs (leaving Order Date to be part of the partitioning), and has enough detail to sort each Seller Key within each month.

Top 20% and Remaining Flag

This table calc uses a slightly different calculation for determining the Top 20% or Remaining and returns an integer. This is because Tableau does not currently let us evaluate table calculation strings in IF statements. The table calc is set up to only return two values within each partition (Month of Order Date) and Null for everything else. This calc has an Advanced Compute Using of Order Date + Seller Key, sorted on Revenue/Descending/Sum.

Top 20% and Remaining

This just returns the text string based on the Top 20% and Remaining Flag.

Revenue Total for Bucket

Ideally, we'd be able to partition the total for each bucket (Top 20% and remaining) on table calc results, however Tableau does not allow us to do this, so this calc also uses an IF statement based on the Top 20% and Remaining Flag and WINDOW_SUM()'s to generate the total revenue for each bucket. This is a nested table calc that has the same Compute Using of the Top 20% and Remaining Flag.

In the \$\$ bar chart view, you can see the result, Tableau is only drawing 12 marks. The addition to make that happen is to put a copy of the Top 20% and Remaining on the Filters Shelf to filter out Null values. With the sorting happening in the table calcs, we can get the big bar segments on the bottom and small bar segments on the top.

Alternatively, in the \$\$ bar chart alternate sort view, I set up a sort on Order Date + Seller Key and just set the Compute Using for all the table calcs to Order Date + Seller Key. This puts the small bar segments on the bottom, like in your original view.

I also set up a % Revenue Total for Bucket Calc to generate those bars, you can see that in the % bar chart view.

Finally, the last two worksheets show an advantage to working this way. In the "orig area" worksheet, I took Sheet 3 and changed the Mark Type to Area marks. This draws many area segments. In the "\$\$ area worksheet", I did the same to the \$\$ bar chart worksheet and that makes a clean area chart. That result can also be used for Line, Square/Circle/Shape Marks, and even a Pie Chart.

Let me know if this works for you!

Jonathan

• ###### 2. Re: How to sort into groups when using the "level of detail" field

Thanks Jonathan.  Very helpful.  Since we only send you the view results data, we had to make some changes on our side to take into account multiple sellers per month.  So instead of using the concatenated field, we just went back to using seller key and month explicitly in the "compute using."

That said, other strange issues arose.  The calculations showed different results whether we had the order date (in months as our column field) as continuous or discrete.  Took a while of trying different combinations of just about everything to discover that changing the compute value in the color table made the numbers correct again. Instead of computing on seller key and month, just choosing seller key was the ticket.

Why on earth would that happen?  Any ideas?

Thanks again for taking the time to send us this.  It was extremely helpful.

• ###### 3. Re: How to sort into groups when using the "level of detail" field

Hi James,

Based on your description of what you did, I'm very concerned that the calculations are now giving you incorrect results. I set up something that worked with the data you posted, since you apparently changed the underlying dimensionality of the data (multiple sellers per month) that could totally change the results of the calculations, and because the calcs are fully optimized (i.e. only acting on the first row in each real or fake partition, hiding null values) the changes in results aren't necessarily obvious. In addition, the table calcs were all set up to have the same compute using, changing one table calc's compute using can make for big changes in results. And finally, removing the concatenated field changes the sort of the view, so each seller key will be assigned to only one bucket for the entire system, not a separate bucket for each month. This was part of the initial issue in the first place where the bar chart had intermixed lines.

The situation that you ran into moving from the date from discrete to continuous was Tableau's data densification coming into play. However, without seeing your view I can't describe exactly which form of densification was happening. A key thing here is to look at the number of marks in the view, if that changes as you are changing compute using settings, mark types, pill types, etc. then you're seeing densification in action.

I'd be happy to review your final workbook, if it's not possible for you to post that you can email me separately at jonathan (dot) drummey (at) gmail and/or we can set up a webex.

Jonathan

• ###### 4. Re: How to sort into groups when using the "level of detail" field

I was concerned the numbers were wrong too.  But we had the numbers verified before the sorting issue, so we were able to compare the results of this method vs. the one we used that couldn't sort.  The results are correct until you drag the field designed for the color into the color shelf.

javascript:void(0);

I've attached a workbook with the detailed data behind it.  This will make more sense.  There are five pages:

1. A good chart with the months as continuous

2. An incorrect chart using the same settings as chart 1, but making the months discrete

3. A good chart with discrete months, but no color field.  You can see the individual cells and note that they have the correct revenue amounts.

4. Chart 3 above, using the color calculated field, but at the "seller level" only.

5. Chart 3 above using the "flag" field for the color field.  Even though this field is used as the basis for the color field (if flag ==1 then...) using the flag field works great, but the field intended to be used as the color field does not.