Ever wish you could just make a combo chart or easily get the totals for “Top N and Other,” and isn’t there really a better way to look at day-week-month and year-level data?  While there's no perfect solution yet, here are some approaches you can try.

There are often several ways to get the solution you need using Tableau, and the “Best” is always the one that you are most comfortable with. This is just how I would do it, but if you have a different approach, then use it.

Section 1 – Top N and Other 

Section 2 – Variable Date levels

Section 3 – Bar and Line Comb Charts

Section 4 – Waterfall Charts

Section 5 – Filtered and Unfiltered Dimension

Section 6 – Filtering Table Calculations    

See the video Link to YouTube recording

1 Top N and Other

A very frequent question is where the user needs to see the Top N elements in a category based on a measure and then group all the remaining elements into a single entry “Other”.

I will show two solutions. The first is a quick solution that works in basic cases, and the second that goes deeper and applies to more complex cases:

1.1 Top N with Sets

I am using the Superstore dataset – you have it – it came with your copy of Tableau. Follow along:

The user wants the ability to select the number of elements to see individually (N) and then Group the rest.

We need a parameter – a simple Integer parameter that will accept any value will do:

Next, create a set on Sub-categories –  based on the sum of sales.

Sets, unlike filters, retain all the data in the dataset, just create two groups – the IN group, in our case, the Top N, and the OUT group, all the other sub-categories.

Now we just need to change some labels, hide what we do not want to see, and add for formatting:

This formula returns the sub-category name for the Top N and “Other” for the rest.

if [Sub-Category]  in [Sub-Category Set] then [Sub-Category] else "Other" end

Then add the labels and the Set (hiding the header) to columns and Sales to Rows. Add grand totals and subtotals, and you are done – NOTE sets and top N are calculated in the same step of the Order of Operations as Fixed LOD’s – If you are applying filters that you want to effect the Top N the filter must be in Context – if you need a refresher on the Order of Operations see: Link to post

1.2 Top N with Ranking Functions

Some users will want to see the Top N by year – not overall. That is a much more complex problem, which I like to solve with a ranking function. Ranking all its forms is a table calculation (they look across rows or columns of the worksheet data table), so identifying the top element and total will require nesting table calculations. –

The starting point is the same, except I have added years to columns, and we want the top N subcategories in each year.

The subcategories will not be the same each year, so we will introduce some blanks (nulls) into the table.

I like to use an LOD to get the subcategory sales by year:

{ FIXED year([Order Date]),[Sub-Category] :sum([Sales])}

Then a rank by year:

RANK_UNIQUE(sum([lod data subcat sales ]),'desc')

And next a Boolean (T/F) that identifies which subcategories are in the top N each year.

[rank based on LOD sales by year ]<=[N for top N by Sales]

And this calculation to assign labels to the subcategories and the “Other” group (Note the ‘ ‘ space leading the subcategory label is important to sort the results)

if [param driven rank ] then ' ' + min([Sub-Category])

else "Other" end

Now, getting the subtotals and totals is not straightforward. The total for each subcategory is just its sales, but the “Other” is the total for the entire year minus the total for the Top N –

The total for the year is a simple LOD:

{fixed year([Order Date]):sum([Sales])}

The total for the Top N is which says in words – the subcategory is in the top N, they include the Sales in the window_sum, but exclude those not in the top N.

window_sum(   if [param driven rank ] then sum([Sales]) end  )

So, the total for Other is:

if [param driven rank ] then sum([Sales]) else

 sum({fixed year([Order Date]):sum([Sales])} ) – [window_sum subcat total ]  end

(Sorry for the series of formulas – I encourage you to download the workbook and reverse engineer the logic)

The resulting viz is where we need to hide the list of “Other “subcategories and only see one of the Other Totals.

Add a “Show Hide “ filter – because we are using table calculations, we need a filter based on a table calculation – that is.

if LAST()=0 then "Show" elseif  [rank based on LOD sales by year ]<= [N for top N by Sales]then "Show"  else 'Hide' end

That is a lot of work – So if the set solution meets your needs, use it – if not, feel free to use this one as a template.

2 Working with Variable Date Levels

We’ve all been there. The team is reviewing your latest viz when someone says, “ Great, but I would like to vary the date level from Day to Week, to Month, and to Year.”  

2.1 Use the Built-in Filter

First, you can take advantage of a built-in function:

Use a continuous date field on columns and add a filter. Open the filter and select “Browse Periods”  from the dropdown menu.

.

The user can select the level from the filter, and the viz adjusts accordingly.

If that meets the need, then stop here –

2.2 Parameter Driven Date Level

As an alternative, you can drive the level of detail in the viz using a parameter.

Create a String Parameter:

The parameter values MUST be the same lower-case values used in the date function –

Then the parameter values are used in this formula:

datetrunc([date level to show ],[Order Date])

As before, add a continuous date to columns, and you can change the level of the viz with the parameter.

Maybe there are times you would like to use a discrete date for the chart. You can change the date you placed on columns to discrete:

And use it as any other discrete date, but now you can dynamically change the level.

If the user still needs more flexibility, I recommend using Dynamic Zone Visibility  – It’s too long to present here – see Link to DZV for a detailed example of how to use DZV  

3 Bar and Line Combo Charts

The Excel “Combo” chart is one function I miss. How about you? It will take some work, but you can achieve a similar look in Tableau.

When you first learned to create graphs, you manually set the X and Y axes and plotted any point at the combination of its x and y values. Typically, in Tableau, placing a Dimension on the Column shelf creates the x-axis – it can be continuous, like dates, or discrete, like categories. The Y-axis results from the range of values from the measure placed on the rows shelf.

To create “combo” charts, we need to use the x-axis location for more than one Dimension. There are only two layers (dual axis), so we need to force the multiple dimensions into two for plotting purposes.

3.1 Stacked Bar and Single Line Chart

We can use Measure Names and Measure Values to help when plotting a stacked bar on one of the dual axes and then add a single line on the other axis  –

The problem arises when we need a side-by-side bar chart and multiple line charts (i.e., Combo Chart)

The “Bar” measures plot individually, but the line plots as a point, which cannot be connected to form a line.

3.2 Side-by-Side Bars and Multiple Line Chart

We need a solution that visually separates the individual bars while allowing a line to connect the points from other dimensions. I will show two options. Neither is “Exact,” but in most cases will visually suffice.

Start by pivoting the Measures to create a single Dimension (Pivot Names) and a single Measure (Pivot Values) –

Next, we break the Pivot Names into two parts – those we want as Bars and those that are Lines – Wait, you say – we just pivoted them to a single dimension, then break them apart – isn’t there another way? Maybe,  if you find one, please feel free to use it.

I want the two averages as lines on the chart and the other three as bars. This will do:

if [Pivot Field Names] in ("ASP", "AVG cust purchase") then "Line"

else "Bar" end

We need the Bar Values and the Line values, so I added two calculations.

Bar Values:

if [bars or lines]="Bar" then [Pivot Field Values] end

Line Values:

if [bars or lines]="Line" then [Pivot Field Values] end

3.3 Option 1 – Fixed Spacing

The two options vary in the way we vary the spacing between bars and the lines on the x-axis. Our x-axis is a continuous date dimension, and we create separation by offsetting each bar or point by a fixed number of days. Visually, it worked out that 5 days between bars with the line point in between worked well.

I hard-coded the values with:

CASE [Pivot Field Names]

when "Sales" then dateadd("day",0 ,[Date])

when "Quantity" then dateadd("day",5 ,[Date])

when "Number of Customers" then dateadd("day",10 ,[Date])

when "ASP" then dateadd("day",3 ,[Date])

when "AVG cust purchase" then dateadd('day',8,[Date]) end

The viz is a dual-axis plot – bar values and line values – Add the Pivot Names (our pre-pivot Measure) to color.

The plot looks good, and if that meets your needs, you can stop here.

3.3– Option 2 – Index

If you want to avoid hard-coding, there is another option: using Index(). I first added a Spacing Multiple parameter – Just any integer value.

I use Index() to separate the marks and the space multiple parameter to adjust the visual as required in the Modified date field.

DATEADD('day',index()*[Space Multiple],min([Date]))

Place the modified date on the column shelf and set up the table calculation for Index as shown.

And to create the viz, we need the original dates on Detail, and pivot value on Color, and the Bar Values and Line Values in a dual-axis chart.

More complex than a simple chart type, true, but you can do combo charts in Tableau.

4.0  Waterfall Charts

Waterfall charts are popular with users who like to follow the progress of sales or profits over a period.

Here, the user wants to see the progression of profit in the Chair subcategory over a 2-year period. There is a combination of positive and negative values in the mix that should be identifiable in the result.

Start by adding a running total to the rows shelf and changing the chart type to a Gantt chart.

Getting close – we need to add this to the Size tile (the negative of the metric in the waterfall)

And the finished waterfall is:

Not difficult, just needed to know how it is created – try it.

5.0 Simultaneously,  Use Filtered and Unfiltered Dimension

Users will often ask how to apply a filter, but also use the unfiltered value of a dimension in the same viz. The solution is a direct application of the Order of Operations and its effect on Fixed LODs.

Context filters are applied in step 3 of the Order before the Fixed LOD is calculated. Dimension filters are applied later in step 5. So, the LOD here is determined is Step 4 of the order:

{ FIXED [Category]:sum([Sales])}

Any dimension filter applied in step 5 will affect any simple calculation, but not the lod:

Now you know how it's done.

6.0 Table Calculation Filter

One last chart to review – the use of Table Calculation filters on running totals. Table calculations are last (step 10) in the Order of Operations. At that point, all the Context, Dimension, and Measure filters have been applied. But you still want to see only a portion of the visible data you created without eliminating any of the data. It's like looking through a window at only a portion of the viz. That can be done with Show – Hide filters based on a table calculation function.

This is simply a running total quick table calculation applied to Sales.

.

The user wants to apply a filter to see only in the last 12 months. The easiest way is with a last() Show- Hide filter.

if  LAST()<12 then "Show"

else "Hide" end

Placed on the filter shelf and set to “Show,” and our virtual window has moved so we only see the last 12 months.

Other users may want to be able to select a range of dates. That can be done using a Lookup() calculation that returns a date:

lookup(min([Order Date]),0)

Place the lookup date on columns and filter, and the Order Date from the data on Detail.

The filter is applied to the Lookup date.

That is all there is to it.

Hope this helped. It gets easier with practice, so use them frequently in your work.

The workbook with all the details can be downloaded from my Tableau Public site at Link to workbook 

Jim