Charting account balances in Tableau

Posted by on Oct 22, 2012

Balance of 4m... yeah right I wish!I’ve seen a couple of questions recently on how to visualise account balance data whilst still being able to use the date aggregation in Tableau. Because balance represents a snapshot in time, it is nonsense to sum up all the balance values over a year or a month. The average or maximum or minimum over a period might be useful but what was really wanted was the balance at the end of a certain time frame – i.e. balance at the end of 2009, balance at the end of February 2010, the balance at the end of Q3 2007 etc.

This post looks at one way to achieve this using a table calculation called “LAST”. LAST will look at the order of your data and label them backwards, giving the last data point a zero and the second to last a one and so on.

Before we start, it is important to make sure your data is in the correct format. Firstly, there must be a unique identifier for each row – usually this will be the date/time record for the account balance, but there cannot be multiple entries for 17/10/2012 unless you have a time element to the field as well (i.e. 17/10/2012 09:30:00 and 17/10/2012 10:00:00 etc.). Secondly, as with preparing all data for use in Tableau, make sure there are no gaps, subtotals, summarised tables etc. See more on the subject in this Knowledge Base article here.

1. Connect to your data and ensure that your date/time is a dimension and Balance is a measure. Try adding Date to the columns shelf and Balance to the rows shelf and change the mark type to a bar chart.

By default, Tableau will sum up all the balances per year which is obviously wrong. We need to tell Tableau to only sum up the last balance per year and that’s where the LAST function comes in.

We only want the LAST = 0 value2. Create a calculated field called ‘Last’ and write the formula LAST() in the box and click OK.

3. Change this field to be discrete by right clicking it and selecting ‘Convert to Discrete’

4. Add this field to the filters shelf and select 0 from the options available

Whoa, that’s not right! What we see now is the last years’ worth of balances all added up together. Tableau guessed that we meant the LAST data point on the Tableau table on the screen, but we need to tell Tableau that we actually mean the last data point in the Date field, per year. To do this we need to edit the table calculation.

5. Add the Date field to the level of detail shelf and change it to show Exact DateTable calc configuration

6. Click on the Last pill on the filter shelf and select Edit Table Calculation

7. In the dialogue box, change ‘Compute using:’ to Date and ‘Restarting every:’ to Year of Date and click OK. Reselect 0 from the filter box if it reappears.

Hey presto, we now have the balance at the end of each year! If we want to show per quarter, go into the Edit Table Calculation dialogue box again and change ‘Restarting every:’ to Quarter of Date. For months, change it to Month of Date and so on.

If you want it to be a bit more slick and interactive for your users to change the level of granularity, you can add a parameter and a calculated field which returns the “datepart” depending on what is selected in the parameter. The LAST table calculation should then be set to ‘Restarting every:’ datepart. See the below viz for a closer look.

Not how you’d do it? Add a comment below or get in touch.

4 Comments

  1. A challenge would be to change the mark type from a bar to a line and retain the same interaction. The way Tableau 7 works with domain padding based on mark type and pill arrangement makes this difficult, and requires a different approach and the Last=0 filter (it was easy in version 6).

  2. Thanks for your comment, Joe. I admit that that was something I couldn’t work out in the time I had to create this post. Perhaps an idea for a follow-up! :)

  3. Here is one option: http://public.tableausoftware.com/views/Chartingbalancesjmedit/Asaline

    The key is understanding the factors that affect how Tableau draws and connects marks when using the line mark type, in my opinion it is overly complex. Get in touch if you want to discuss the details of why other more common intuitive routes do not work.

  4. A-ha I see what it’s doing there. Funnily enough I started out the workbook like that (using if last=0 then sum(Sales) end) but took it out as I wanted to simplify the explanation for the blog. Thanks for the example!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>