5 Replies Latest reply on Jul 17, 2013 8:19 AM by Jonathan Drummey

    Create a statement from deposit and withdrawal data

    Eric Tobias

      I'm trying to create the equivalent of a account statement chart in Tableau when only the deposit and withdrawal data is available. I am able to do what I want to do if I create a "balance" set of data, but am having difficulty creating the same information from only one data source of withdrawals and one of deposits. I know I need running totals (a.k.a. table calc), but can't seem to figure out how to get the table calc to work properly.

       

      The data is simple: for deposits, date of deposit and amount of deposit; for withdrawals, date of withdrawal and amount of withdrawal. To oversimplify, the balance is the sum of deposits-to-date minus withdrawals-to-date.

       

      I put together a workbook (attached) to help reduce the problem to its simplest form. My goal is to produce something like the "Balance" worksheet, but without having to use the "Balance" data source. I could obviously create the balance data source and resolve my own needs, but the balance data is just an extra extraction of the deposit and withdrawal data.

       

      Any ideas?

        • 1. Re: Create a statement from deposit and withdrawal data
          Jonathan Drummey

          At the very minimum you're going to need a scaffold source of dates - it doesn't require the transaction information. It can have as few as two rows and make use of Tableau's domain padding, or as many as a row for every day, or something in-between that has at least a row for every date of withdrawal and/or deposit. From that scaffold source you can then use Tableau's data blending to blend in the deposit and withdrawal info.

           

          There are a lot of ways to generate the scaffold, from just making a file with a list of all dates (or just two dates), to using Custom SQL, etc. and those ways can be more (and less) dynamic. You didn't specify the data sources (what kind of format do they come in, is it in separate spreadsheets in one Excel file, different workbooks, etc.) nor how dynamic you want the scaffold to be, so I can't give you more guidance at this point.

           

          Jonathan

          • 2. Re: Create a statement from deposit and withdrawal data
            Eric Tobias

            Generally, the "scaffold" would be from the first deposit date and continue through the date the dashboard is updated. The data source comes from separate Microsoft Excel workbooks, although that can be modified depending on the easiest way to accomplish this. Does this help?

             

            In the past I've created a "date" table that can provide the underlying structure for database queries, so I'm familiar with what you're referring to (I think). The date table is great, but the dashboard has to be able to "start" on a per-client selection and end on a per-client selection (e.g., user-specified end date, statement date, etc.).

            • 3. Re: Re: Create a statement from deposit and withdrawal data
              Jonathan Drummey

              See the attached for an example. I used Custom SQL on the Deposit data (I duplicated the data so I'd be able to do that) to create a two row data source, one for the start date (the date of 1st deposit) and another that is recomputed to be today's date. If your Deposits Excel file has more than one client in it, then I'd add a unique client id to the query and as a group by for both SQL queries, so the scaffold data source would have 2N rows in it, where N is the number of clients.

               

              Then I turned on domain padding via Show Missing Values, added the two secondary sources via data blends and set the Compute Using of the table calcs to be the Date. I also generated a table calc filter that can be applied to the data and retain accurate results.

               

              However, if you want multiple worksheets on your dashboard to all have the same date filter, then you're going to need to use some parameters to choose the date range, then make the table calc filter aware of those parameters. This is because table calc filters can't be applied to multiple worksheets at once in the way that filters on dimensions and regular aggregates can be.

               

              Jonathan

              • 4. Re: Create a statement from deposit and withdrawal data
                Eric Tobias

                Thanks, Jonathan... took me some cycles, but I was able to adapt your input to meet my need! Thanks so much for your help with this and your contributions overall. I regularly get helpful information from your posts throughout the forums.

                • 5. Re: Create a statement from deposit and withdrawal data
                  Jonathan Drummey

                  You're welcome, and thanks for the appreciation!