2 Replies Latest reply on Jul 8, 2018 7:22 PM by Yves Lacroix

    How can I group list of bank transactions by sub-category similar to personal budget heathers?

    Yves Lacroix

      I have imported my bank statement into Tableau.

       

      I want to see the various transactions types below as 'Dimension' and Measures' into Tableau so that I would be able to sort them by types of transactions and sum them up.  For example: Insurance, Investment, Deposits, Mobile expenses, etc. summed up by month and the entire year.

       

            

      Bank AccountTransaction DateAmount of transactionName or type of transactions
      Y & C L x569***01/02/2018-224.68INS Intact Assur.
      Y & C L x569***01/02/2018-33.31INS Intact Assur.
      Y & C L x569***01/02/2018-7600Chèque n° 692
      Y & C L x569***01/03/20183800MTF Invesco
      Y & C L x569***01/03/2018327.99CAS MFQ-SAE
      Y & C L x569***01/03/2018-290BPY CSA
      Y & C L x569***01/04/20182014.16Mobil Dépôt
      Y & C L x569***01/05/20182144.82PAY XYX
      Y & C L x569***01/05/2018-80Virement Interac-Envoyer des fonds
      Y & C L x569***01/08/2018-240.6INS Intact Assur.
      Y & C L x569***01/10/2018-232.75Virement Interac-Envoyer des fonds
      Y & C L x569***01/11/20183653.21PAY XXX
      Y & C L x569***01/11/2018-75Virement Interac-Envoyer des fonds
      Y & C L x569***01/11/2018-5316.29Paiement MASTERCARD CAPITAL ONE 522303******1888
      Y & C L x569***01/12/2018-26.22TPV Achat COUP DE PEIGNE 25764801
      Y & C L x569***01/13/2018-750Virement Interac-Envoyer des fonds
      Y & C L x569***01/13/2018-1Virement Interac-Frais
      Y & C L x569***01/15/2018-255Virement Interac-Envoyer des fonds
      Y & C L x569***01/15/2018-1Virement Interac-Frais
      Y & C L x569***01/16/2018-180BPY CSA
      Y & C L x569***01/16/2018-200Invesco

       

       

      When I import the above file into Tableau, I get the following Dimensions and Measures:

       

       

      Screen Shot 2018-07-08 at 1.22.42 PM.png

       

       

      Do I need to prepare the data differently to get to do this?

       

      The end result should resemble something like this:

       

      Revenues:

      -Salary

      -Investment and interest return

       

      Expenses:

      -Insurance

      -Grocery

      -Car expenses

      -etc.

        • 1. Re: How can I group list of bank transactions by sub-category similar to personal budget heathers?
          Ken Flerlage

          Not quite sure I understand the issue. The structure of the data, as is, should allow you to do what you need. For instance, I pulled in your data and did a quick summary:

           

          I suppose the only thing that is missing are the specific categories you need. If you don't have those categories in your data, you have a couple of choices. First, you could add it to your data. Second, you could use groups in Tableau to create those. The problem with this is that new transaction types will not be automatically added to those groups, so it will become a maintenance problem.

           

          To create the groups, right click on "Name or type of transaction" and select Create then Group.

          From here, you can choose how to group each item:

           

          This will create a new data item in your data called Name or type of transactions (group).

           

          You can then use this in your charts. For example, this shows your spending over time using some groups I maade up.

           

           

          Is this what you're looking for?

           

          See attached workbook.

          • 2. Re: How can I group list of bank transactions by sub-category similar to personal budget heathers?
            Yves Lacroix

            Hi Ken. Thanks for your assistance.  I just tried to use the main data that I wanted to use and tried to follow your example.  I still don't get the list of 'Name or Type of transactions' that we you obtained it, which what I am looking for.  Instead, I get the following.  I'm quite certain that I am close to the solution that you presented.  Instead, I obtain the following.  What do I need to do to get your version with the list of all transactions and Sum for each type of transaction?

             

            Thanks again for your assistance!

             

            Screen Shot 2018-07-08 at 9.51.27 PM.png