End-of-Period Totals

Totals in Tableau have the ability to aggregate your data a number of different ways, and it takes care of 90% of the use cases for totals.  Often, however, people want to look at end-of-period totals.  Imagine you run a store and count inventory at the end of every week.  You may want to build a crosstab that shows inventory at the end of each week, but also add totals for the month, quarter, and year level.  The yearly total obviously shouldn’t show the summation of every week for the year…that doesn’t help you figure out what you have on-hand.  Don’t worry, its easy possible in Tableau!

See how the last week of each month matches the total below it.  In the yellow box, we see the last week of the quarter matches the monthly AND quarterly totals.

The end-of-period total is useful for inventory managers, any type of finance (end of day balances), and anybody tracking non-additive time series metrics.  Before we get to the solution, it’s important to understand how totals work.  Once you’ve got that down, the rest is just figuring out logic and syntax.

The logic is simple.  If we’re looking at a single cell (the granularity of your data), just return that data.  If we’re looking at any total, only return the data from the most recent date.  Normally, we’d cohort out the most recent date using something like the below.

RUNNING_MAX(ATTR([Date]))

We’d then need to compare that date to a row-level date, though.  There’s our problem.  If you try the below calc, you’ll end up with an error for comparing row-level data to aggregate data.

IF RUNNING_MAX(ATTR([Date])) = [Date] THEN [Inventory] ELSE 0 END

Table calcs will always return aggregates.  What we need to do is find a way to return the running-max date as a row level result so we can use it in comparisons.  To do this, we can use EXCLUDE or FIXED.  Normally, I use FIXED equations when given the option, simply because they’re less viz-dependent.  You can build one calc and use it on 15 different sheets, almost regardless of their dimensionality.  In this case, however, we’re custom-building a calc for the sheet anyway.  FIXED has funnier behavior with filters, so in this case I’ll use EXCLUDE.

The first thing we need to do is ensure that we return the appropriate value for individual cells.

IF COUNTD([Week-ID]) = 1 THEN SUM(Inventory) 
END

Next we need to take into account monthly totals.

IF COUNTD([Week-ID]) = 1 THEN SUM(Inventory)
   ELSEIF COUNTD(DATEPART('month',[Date])) = 1 THEN 
       SUM(IF [Week-ID] = {EXCLUDE [Week-ID] : MAX([Week-ID])} THEN [Inventory] END)


END

Looking at the above calc, we accomplish a couple of things in sequence.  First, we need to identify the monthly total.  We know all single cells were caught by line 1 of the calculation, so anything else will be a total.  We specifically want to identify a monthly total, though.  Simply checking if COUNTD of months = 1 will ensure we’re looking at the monthly total.

Next, we need to isolate the most recent week.  We do this using our EXCLUDE function.  Our viz is calculating the the level of year, quarter, month, and week.  We need to find the highest week-id of the month.  To calculate at that level, we simply need to exclude week-id from the calculation.

Now we’ve identified the highest week value, we need to find the inventory value associated with it.  This is where our if-then logic comes in.  Only one week will match the highest week-id, so IF <Exclude function returning max week id> = [Week-ID] THEN [Inventory] END returns the inventory associated with that week.

Month totals are now computing correctly, but quarter totals are wrong.

Looking at the above image, you see we’ve successfully fixed some of our totals!  There’s still some to go, however.  The quarterly total is currently displaying as the sum of our monthly totals…and this makes sense.  Remember, our EXCLUDE statement returned data at the level of year-quarter-month, excluding week.  When Tableau queries for this total, it is returned 3 numbers (one for each month) and then sums them together.  To get our quarterly numbers to compute correctly, we need to add the below into our calculation.

ELSEIF COUNTD(DATEPART('quarter',[Date])) = 1 THEN
    SUM(  IF [Week-ID] = { EXCLUDE [Week-ID], DATEPART('month',[Date]) : MAX([Week-ID])} THEN [Inventory] END)

This takes care of our quarterly totals, but our yearly totals will still mis-compute.  You’ll need to add one of these clauses for each level of totals that you want to display, and add them sequentially from most granular to most aggregate.  In the end, your calc should look like the below.

IF COUNTD([Week-ID]) = 1 THEN SUM([Inventory])
ELSEIF COUNTD(MONTH([Date])) = 1 THEN 
  SUM(  IF [Week-ID] = { EXCLUDE [Week-ID] : MAX([Week-ID])} THEN [Inventory] END)
ELSEIF COUNTD(DATEPART('quarter',[Date])) = 1 THEN
SUM(  IF [Week-ID] = { EXCLUDE [Week-ID], DATEPART('month',[Date]) : MAX([Week-ID])} THEN [Inventory] END)
ELSEIF COUNTD(YEAR([Date])) = 1 THEN
SUM(  IF [Week-ID] = { EXCLUDE [Week-ID], DATEPART('month',[Date]), DATEPART('quarter',[Date]) : MAX([Week-ID])} THEN [Inventory] END)
ELSE
SUM(  IF [Week-ID] = { EXCLUDE [Week-ID], DATEPART('month',[Date]), DATEPART('quarter',[Date]),DATEPART('year',[Date]) : MAX([Week-ID])} THEN [Inventory] END)
END

There you have it.  This calc returns your most recent value as your total, instead of accepting Tableau’s defaults.  Because we used EXCLUDE, it will respond to all of your filters, regardless of it they’re on context or not.  I’ve found this solution to hold up fairly well in production, but keep in mind that if you’re running this against billions of rows of data, it may slow down.  Most end-of-day datasets don’t grow to that size (there’s only been about 10,000 days since the world wide web became publicly available), so plug this in and let me know how it works!

Custom Totals in Tableau

Tableau has built-in functionality to compute totals for almost any visualization.  Simply drag Totals onto your viz, and Tableau will add a totals portion to the end of your viz.  At first glance, you might think these totals were all computed similarly to table calculations – Tableau looks over the results of the query and computes based off of that.  It’s a simple two-pass aggregation and Tableau can do it all in memory.  If you tinker around a little more, however, you’ll find that that’s not the case.  Take the below example.

avgofavgs

We’re looking at AVG([Sales]) in three separate categories, and it returns 349.7, 119.3, and 452.7. The reference line (an average line) returns 307.3.

Now let’s ask (seemingly) the same question a different way by removing the reference line and adding totals.

avgoverall

The total returns a different value… 229.8.What’s happening here?

Well, if we sum up the three values (349.7 + 119.3 + 462.7 = 921.7) and divide by 3, we get 307.3.  The reference line is returning an average of averages (and somewhere, a statistician rolls over in his grave).  The total bar, however, returns 229.8.  Removing Category from the Rows shelf shows us that Tableau is returning the overall row-level average of sales.

onebaraverage

What this proves to us is that Tableau is actually going back to the database and sending a separate query for the totals.  What this means for us is that we can cheat totals.  If we know that Tableau sends a query back for totals, we could create a calculation which returns different results for the totals bar.

The first thing we need to do is find a way to flag the totals bar in the query.  Because my above visualization shows one bar per category, we know that COUNTD([Category]) will return 1.  The totals bar, however, will return 3.  Putting the logic into a calculation allows us to return…well…anything as our total.

Sales looking bad?  Try the below!

IF COUNTD([Category]) = 1 THEN SUM([Sales]) ELSE MIN(10000000000) END

On a more practical side, however, it allows us to customize the behavior to what we need.  Maybe you want each individual bar to display the average sales, but the grand total bar to display the total sales.

IF COUNTD([Category]) = 1 THEN AVG([Sales]) ELSE SUM([Sales]) END

Any (aggregate) business logic you want can now easily be built in there.  As with any Tableau hack, of course, it leads to a couple more hacks as well, including custom waterfall charts and custom end-of-day totals.

Date Filter Extensions in Tableau

Tableau handles dates fairly flexibly – it allows a variety of input formats, handles them as hierarchies naturally, and provides a ton of calculation flexibility for any sort of math or logic.  The two major criticisms I hear from Tableau users are how they interact with a) parameters and b) filters.  The parameter ask is fairly simple – people want parameters to automatically update to “today” when they load a dashboard.  Luckily, there’s a free extension available in the extensions gallery which does exactly this.  It’s built and hosted by Tableau, and the source code is freely available.

Filters, on the other hand, have a lot more options for implementation.  Relative date filters are very powerful, but what if I want to look at a subset of data from last year?  Date sliders are the only way to do that, but they have their own limitations.  A date slider only has two settings for it’s default settings.

  1. Full extent of the data
    1. When the dashboard loads, Tableau finds the full range of your date data and sets the bounds to those dates.  This is great because when you load the dashboard, it’ll take into account all of the most recent data.  The problem arises when you have 5 years worth of data.  Do you really want to query all that when it first loads, just so you can look at this week’s data?
  2. Pre-set values
    1. If you want to make sure that Tableau doesn’t query all of your data, you can pre-set the slider values.  The problem here is that it isn’t forward-looking.  If I publish a dashboard on 1/1/2019 and hardcode the values, then when someone loads the dashboard in the future, the filter will still be set to 1/1/2019 as the max.

So there’s the problem – Tableau allows you to hardcode both sides of of the slider or neitherMost people want to hardcode one side and have the other be dynamic…so that’s the extension we built.  A Tableau Extension is a custom webpage which is added to a dashboard to extend the functionality.  For more info on extensions, check out the Tableau developers page.

Below is a quick tutorial to build out an extension for custom date functionality in Tableau.  The code I’m referencing is available here.  The left-hand side of that page contains an HTML file, a JS file, a TREX file, and a favicon file, which is everything you’ll need to create an extension.

  1. Build a webpage.
    1. An extension is a custom HTML page.  In this case, the HTML page does almost nothing, so it’ll just contain a title and references to the JS we’re using (jQuery, bootstrap, the Extensions Library, our JS file).
    2.  
  2. Add JS functionality.  Everything from here on out is in the JS file.
    1. First thing we need to do is initialize the API (line 1).
  3. Create a function to update your filter (line 5, updateFilterRange()).
  4. Set variables for your start and end points.
    1. We’ll hardcode the starting date to a date of your choice.  This is easily done by creating a date in JS.
      1. let minDate = new Date(“1/1/2014”)
    2. For the upper limit, you’ve got flexibility.  The two major use cases I see here are setting it to “today” or setting it to the highest date in the dataset.  Setting it to “today” is easy, as a Date instance in JS will default to “today”.  The other option is getting data from the workbook itself, which is what we’ve done in the sample code.
      1. Create a calculation in the workbook which returns your highest date.  Using an LOD to return this is simple {FIXED : MAX([Date])}.  Put this calculation on detail on your target worksheet to make it available to the API.
      2. In your JS, we make an API call to return data from your sheet.  This returns summary data, meaning one result per mark on the sheet.  From the table returned, we’ll find your Max Date column.  Because it returns the same result for every mark, we’ll can use Max Date from any mark.  (Note that we could have calculated max date here instead of using an LOD, but we’re taking advantage of the Hyper database instead of iterating through all of your data in JS).  Set this as your maxDate variable.
      3. Invoke the applyFilterRangeAsync function.  It takes 3 arguments: the field you defined at the beginning, the minDate that you hard-coded, and the maxDate returned from our table.
  5. Call the function you’ve created.

tableau.extensions.initializeAsync().then(() => {
  updateFilterRangeDataSource();
});

function updateFilterRangeDataSource() {
  //define our target sheet, field, minimum date, and dashboard
  let fieldName = 'Date';
  let sheetName = 'Timeline';
  let minDate = new Date("1/1/2014");
  let dashboard = tableau.extensions.dashboardContent.dashboard;
  let selectedWorksheet = dashboard.worksheets.find(w => w.name === sheetName);
  //get data back from the workbook to find our highest possible date
  selectedWorksheet.getSummaryDataAsync().then( table => {
    let maxDateColumn = table.columns.find(columnNames => columnNames.fieldName === 'Max Date').index;
    let maxDate = new Date(table.data[0][maxDateColumn].value);
    //make the API call to update the Tableau Filter
    selectedWorksheet.applyRangeFilterAsync(fieldName, { min: minDate ,max: maxDate});
  });
}

6. Add the TREX file to your workbook!

There it is!  15 lines of JavaScript and you’ve built a feature which massively extends Tableau’s default date functionality.  Of course, this is far from the limits of what we can do with the extensions.  If you’d like to build a custom UI for your date filters, there are tons of JS widgets you could incorporate into an extension.  If you’d like any other default ranges, you just need to tweak the variables.  If you’d like to make this more scalable, you can use the Settings namespace in the Extensions API to make this workbook-agnostic.

Overall, though, it’s cool to note what we’ve done here.  The Extensions API is often billed as a way to integrate two applications, or a way to create custom visual elements (and it’s great for both of these things).  To me, however, the biggest wins I’ve had with the Extensions API have been creating functionality that Tableau previously didn’t have, and doing so in an efficient way.