Ultimate Guide to Tables in Tableau: Pagination

Part 1: Pagination

In this post we continue our ultimate guide to tables in Tableau by discussing how to create pagination in Tableau.

Sometimes our tables are too long in Tableau–and sometimes we want to combine sheets too make a single table. We can do this with pagination! And when you think about it, most tables we interact on the web has pagination.

This blog post will show you how to create pagination using the example below.

This solution requires 4 sheets: one for the table, one for the numerical display, one for the left arrow, and one for the right arrow. We’ll start with the table, get it set up, then transition to the arrows, and finally create the display.

For this example you’ll utilize the Sample – Superstore dataset.

Step #1: Build the base table.

Add [Customer Name] to rows. Sort this dimension descending using sum of Sales.

Next you’ll create the measures that will be on the table. You’ll then use [Measure Names] and [Measure Values] to build the table.

This table includes total sales, total profit, profit ratio, and total orders. Sales and profit are base measures, but you’ll need to build profit ratio and total orders. Here is profit ratio:

// Profit Ratio
SUM([Profit])/SUM([Sales])

and total orders:

// Total Orders
COUNTD([Order ID])

Now bring [Measure Names] to columns and [Measure Values] to text. Be sure that SUM(Sales), SUM(Profit), Profit Ratio, and Total Orders are in the [Measure Values] card.

Step #2: Build the framework for pagination

You’ll first create a parameter that will indicate how many rows to show. For this example you’ll specify 15, but you can change it later.

While you are at it, create a second parameter called [page number] which will be the page number, ultimately (but this will come in handy later). Set the integer in the parameter to 1.

Now it’s time to begin building the pagination! First you’ll start with a calculation called [Customer Name | Index]. This function just applies the INDEX() function.

// Customer Name | Index
INDEX()

This calculation will come in handy for building your other calculations. Your next calculation is called [Page | Customer Name | Index]. This will calculate which rows to show on which page.

// Page | Customer Name | Index
((([Customer Name | Index] - 1) - (([Customer Name | Index] - 1) % [rows to show]))/[rows to show]) +1

Now you simply need to create a calculation that shows which page to show. You will create a boolean bases on the page parameter called [Page Number | TF].

// Page Number | TF
[Page | Customer Name | Index] = [page number]

Add [Page Number | TF] to filters and select TRUE. You will now see 15 rows from page 1. This is bases on the [rows to show] and [page number] parameters. This leaves you with the following visualization:

Step #3: Build the Left Arrow

Next you’ll create the left arrow. Create a new sheet and add '◄' to text. Align the text to the center-middle.

Next, create a calculation called [Page | -]. This calculation will subtract one value of the page number unless you are at the lowest number.

// Page | -
IF [page number] = 1
THEN 1
ELSE [page number] - 1
END

Add [Page | -] to detail.

Additionally, create two more calculations: [TRUE] where the calculation is equal to TRUE. And [FALSE] is equal to FALSE. Add these calculations to detail, as we’ll. Finally, be sure to turn off tooltips.

Step #4: Build the Right Arrow.

Next you’ll create the right arrow. Create a new sheet and add "►" to text. Align the text to the center-middle.

Next, create a calculation called [Page | +]. This calculation will add one value of the page number unless you are at the highest page number.

// Page | +
IF [page number] = FLOOR({COUNTD([Customer Name])}/[rows to show]) +1
THEN FLOOR({COUNTD([Customer Name])}/[rows to show]) + 1
ELSE [page number] + 1
END

Add [Page | +], [TRUE], and [FALSE] to detail. A turn off tooltips.

Step #5: Build the page navigator

The page navigator is the most complicated component on the dashboard. We need to display the selected page, the surrounding pages, and make it interactive. Also the selected page might not always be in the middle of the list of 5 so we need to make sure we can highlight the correct page at the correct time.

Start by adding [Customer Name] to detail. Sort descending by sum of sales. Then add [Customer Name | Index] to columns. Change the measure to discrete. Edit the table calculation, select Specific Dimensions and check the customer name.

Each index value is a product and its rank on sum of sales. Let’s add some text to this visualization showing the page number. You’ll also set the selected page number to be a darker color and bolder font.

 

 You’ll create two calculations for the labels: [Label Page Number] for the selected label page and [Label Page Number Not] for the pages that are not actively selected.

// Label Page Number
IF [Page Number | TF] 
THEN [page number] 
END
// Label Page Number Not
IF NOT [Page Number | TF] 
THEN [Page | Customer Name | Index] 
END

Add both of the measures to text on the marks card. Change both to discrete (it helps with number formatting). Edit the table calculations and choose Specific Dimensions and Select [Customer Name] for both measures.

From there and edit the text. Place the two dimensions on the same line. For [Label Page Number Not] select a lighter gray, Tableau Book, Size 12. For [Label Page Number] select black, Tableau Semibold, Size 12.

That leaves you with the following:

The [Customer Name | Index] shows all customers, but we don’t really need to show all the customers, we need to just show a single customers from a single page. The best thing we can do is just show the first customer from each page–since the last page might only consist of that single customer.

Write the following formula called [Page n | 0]:

// Page n | 0
([Customer Name | Index] % [rows to show]) - 1

Move this measure to a dimension and place it on filters. Edit the table calculation of the filter. Be sure Specific Dimensions are used and [Customer Name] is selected. Then select 0 from the filter options. This results in the following visualization:

The last bit is the hardest part. We need to know which pages to show. This is about setting a range: a lower-bound and an upper-bound. We will do that with two calculations called [threshold | bottom] and [threshold | top]

// threshold | bottom
IF [page number] < 3
THEN 1
ELSEIF [page number] > FLOOR({COUNTD([Customer Name])}/[rows to show]) - 1
THEN FLOOR({COUNTD([Customer Name])}/[rows to show]) - 3
ELSE [page number] - 2
END
// threshold | top
IF [page number] > FLOOR({COUNTD([Customer Name])}/[rows to show]) - 1
THEN FLOOR({COUNTD([Customer Name])}/[rows to show]) + 1
ELSEIF [page number] < 3
THEN 5
ELSE [page number] + 2
END

And finally we’ll build a single calculation that will define the upper- and lower-bounds in a single calculation.

// theshold | tf
[Page | Customer Name | Index] >= MIN([threshold | bottom])
AND 
[Page | Customer Name | Index] <= MIN([threshold | top])

Note: the values here are hard-coded so that 5 pages will show at any given time.

Add [threshold |tf] to filter. Edit the table calculation, choose Specific Dimensions and select [Customer Name]. Then set the filter to True:

Now you just need format by removing row dividers and hiding headers. And align text to the center-middle. Finally add [TRUE] and [FALSE] to detail.

Step #6: Build the Dashboard

Add the table to the dashboard, then add a horizontal container below the table. Add the left arrow, display, and right arrow into the container. Remove all the inner and outer padding associated with each of the three sheets. Set an outline around the container and add a background color to each of the arrows.

Step #7: Add dashboard actions

Next you should add dashboard actions to the left arrow, display, and right arrow. You will also apply an automatic deselect to each of the three sheets, too.

The first the parameter action will be for the right arrow. Change the [page number] using [Page | +] with no aggregation on select.

The second the parameter action will be for the left arrow. Change the [page number] using [Page | -] with no aggregation on select.

The the the parameter action will be for the display. Change the [page number] using [Label Page Number Not] with no aggregation on select.

Finally we need to make 3 separate filter actions, one for each sheet with an action. On select of the left arrow drive an action to the left arrow sheet. On deselect, show all values. Set the target filters on selected fields when [TRUE] is equal to [FALSE]. Repeat for the display and right arrow.

So whats the final result?

This table:

What's next?

In the next posts we’ll dive changing the number of rows we show on a table.

Like what you are reading?

Get articles sent to you when they are posted.
Comments are closed.