3 Replies Latest reply on Jun 23, 2016 11:42 AM by Simon Runc

    Getting and Extended amount to sum with multiple transactions

    Corey Capps

      I have tried about everything I know and am running into a problem when I am trying to sum multiple transactions based on the month.  Under American Express - Office Supplies there are two transactions 72.28 and 260.64 I need to show this as 332.92.  I cant figure out how to do this with out duplicating my transaction or throwing my numbers off.  I have attached the workbook for some guidance.  I need these transactions to sum and the grand totals to sum for all transaction for the GL and vendor.

        • 1. Re: Getting and Extended amount to sum with multiple transactions
          Simon Runc

          hi Corey,

           

          Aren't you already duplicating the Transaction Rows?...Just had a quick look at your example, hoping we could just use SUM([ExtAmt]), rather than showing each transaction, but got 2663!...upon further investigation it looks like we have multiple rows for each Transaction ID

           

           

          We can (probably) get round this with some LoD Calculation, but just wanted to check you were aware of this...as this looks a lot like a data-joining problem?

          • 2. Re: Getting and Extended amount to sum with multiple transactions
            Corey Capps

            Hi Simon,

             

            I had to do an inner join of two tables that are in my database in order to get the dimensions that I needed for the workbook. I have tried to narrow this down by doing a distinct count on the voucher line distinct key (Voucher Count).  This returned the correct voucher number of records but was unsuccessful in showing the amounts for the transaction count.

             

            Thank you,

             

             

            Corey Capps

            Staff Accountant

            Florida Surplus Lines Service Office

            1441 Maclay Commerce Drive, Ste. 200

            Tallahassee, FL 32312

            Phone: 850.205.6661

            ccapps@fslso.com<mailto:ccapps@fslso.com>

            http://www.FSLSO.com/<http://www.fslso.com/>

            • 3. Re: Getting and Extended amount to sum with multiple transactions
              Simon Runc

              Well at least you know where to look!...I've ended up with duplicate rows after joins many times, and tracing back where it's come from can be a real pain!!

               

              We could 'handle' this in Tableau, by creating an LoD (or using a Table Calculation) so we do a SUM of the MIN([TransactionAmt]) per Vendor/GL Account/Date...but the Grand Totals will need some complicated handling (potentially), and you'll be constantly fighting this in every Viz you build (and from a performance perspective you've got 5+ times as many rows as you need)...so always better to get the data-shape/grain correct, as you'll get that time back 10 fold in the end (and Tableau is far more fun, and less frustrating!!)