9 Replies Latest reply on Sep 4, 2012 5:18 AM by Jonathan Drummey

    Retrieve last data of each group

    Alexander Schmidt

      Hi there,

      I am banging my head on something simple perhaps. I have the following data. It's actually a list showing bank account movement. I want to graph the bank's data situation (closing amount) at the end of each day. Consequently I want to graph the last data value of each group (which is selected by date).

      Can someone help me here; Thanks,

       

      Alexander

        • 1. Re: Retrieve last data of each group
          Robin Kennedy

          Is there a 'Time' column in your data source? How do you know that the last one in the list is the last transaction of that day and it isn't sorted in some other way?

           

          I would most likely create a calculated column in the spreadsheet (or the database) to identify the last transaction, like you have done in your screenshot, then sum this per day. Fairly easy to write a formula in Excel.

          • 2. Re: Retrieve last data of each group
            Frank Forestell

            Good suggestion. I would expect that having it in the database would be best, then all you need to do is filter via the flag.

            • 3. Re: Retrieve last data of each group
              Alexander Schmidt

              The spreadsheet I am using is automatically generated by the accounting software into an Excel spreadsheet. As a result I want to keep it as is because I can't see myself ask the no-computer literate accountant to edit every day the excel file. This would make no sense!. Yes, there is a date column to the left of the spreadsheet and the balance column calculates the new bank balance automatically. As a result, all I really want is to take the LAST balance situation for every group (a group is determined by the date). Just like with stocks where you have the day's high and lows and the closing rate, all I want is basically the closing rate for each day which is the last entry in the list of each day.

              Can someone please tell me how I could calculate this. Ultimately, the ideal situation would be to have a functional LAST(of the group balance value).

               

              Can anyone help me here?

               

              thanks,

              • 4. Re: Retrieve last data of each group
                Frank Forestell

                Hi, Alexander. I tried using a combination of Max() and Index() but it just won't work within Tableau. I think the only solution is for you to include in the algorithm that generates this spreadsheet a column that numbers the balances as they are generated and then you could filter on the Max of this column value.

                 

                Frank

                • 5. Re: Retrieve last data of each group
                  Jonathan Drummey

                  There are a variety of ways to do this in Tableau, my question is, how do you identify the last transaction within the group? In the worksheet you posted the screenshot of, there's an implicit order (top to bottom), but whatever transformations are necessary to get data from the original source into Tableau can end up changing that order, and some of those can be out of our control. In your case, if the entries are time stamped or date/time stamped then that would be enough, or if there was an auto numbering row ID or transaction ID that would work as well.

                   

                  Also, posting some sample data in a .csv or .xlsx would help us to craft a solution that more exactly meets your needs.

                   

                  Cheers,

                   

                  Jonathan

                  • 6. Re: Retrieve last data of each group
                    Alexander Schmidt

                    Hi Jonathan,

                     

                    thanks for your help. I have attached an excel sample of the file. The spreadsheet is a basic accounting data dump that represents accounting data entry and the only order criteria is the date column A. In our example you can see that on the 19/01/2011 there are 7 entries. Column E is putting money into the bank and column G is paying invoices and thus money is leaving the account.

                     

                    The total of all E minus the total of all G  yields the account final situation which is also displayed on the last line in comulmn H.

                    The challenge here is to graph the last value of each group of dates so that the bank graph displays each end of day bank situation.

                     

                    Can you help me with this info?

                     

                    Cheers,

                    Alexander

                    • 7. Re: Retrieve last data of each group
                      Jonathan Drummey

                      Hi,

                       

                      Based on the data you posted there's no algorithmic way I know of to pull the last row of each day, since it has the issues I wrote of in my prior post. However, from the perspective of the visualization I don't think that's what you actually need, which is the a running sum of the Débit - Crédit, summed over each day. In that case we don't really care about the order of the transactions within the day, just the total. So I created a calculated field with RUNNING_SUM(SUM([Débit] - [Crédit])), set the Compute Using to the Closing Date - Table (Across) also works, I like to be specific so the calc won't change if pills get moved/added - and you should be all set.

                       

                      Two additional notes:

                       

                      - Because the data was in DD/MM/YYYY format and my system uses MM/DD/YYYY, I created a calculated field to create the Closing Date.

                       

                      - Tableau has to ways to display dates, as a Continuous value (green pill) that creates an axis with all the dates, or as Discrete values (blue pill) that only show dates that exist in the data. I created two views of the Closing Balance, in the first using a continuous date you can see the flatter line from 1/7/11 to 1/10/11, whereas in the second view with closing date as a discrete you can see a steeper slope as the data goes right from 1/7 to 1/10.

                       

                      Cheers,

                       

                      Jonathan

                      • 8. Re: Retrieve last data of each group
                        Alexander Schmidt

                        Hey Jonathan,

                        Thank you so very much for your help and yes the answer you gave me answers my question. There is one problem however.

                        If a user selects a data range for further analysis, then obviously the data will change as the running_sum will change. This can be dealt with by simply forcing the user to use the zoom buttom.

                         

                        A problem I came across however is a seemingly Tableau bug. From my file which is some 2500 lines, I found that one group of data which in my excel file is 10 is recorded into tableau as being 8. In other words, the tableau script missed 2 lines which obviously screws up the data that follows.

                        Have you any idea why or for what reason this may happen. Check out the group for date= 6/08/2012. Tableau will record 8 lines instead of 10?

                         

                        Thanks again for your time and help!

                         

                        Alexander

                        • 9. Re: Retrieve last data of each group
                          Jonathan Drummey

                          Hello Alexander,

                           

                          To do the filtering without affecting the Closing Balance, you can use a filter based on a table calculation like LOOKUP(ATTR([Closing Date]),0). Filters based on table calculations are applied after most calculations are completed, so the filter "hides" details and doesn't change the results. I've set that up in the attached.

                           

                          When I loaded the Bank data.xlsx file, I still see all 10 records for that date, so I'm not sure why you are seeing only 8 records in your workbook. My first thought is that maybe those two missing records are not getting a proper date assigned, you might create a view with the original Date and the calculated Closing Date and see whether that is the case, beyond that maybe there's an extra filter or dimension in the view? If you can post your workbook and/or a screenshot of the view that would be helpful, if you are not able to contact me at jonathan (dot) drummey (at) gmail.com and we'll figure something out.

                           

                          Jonathan