10 Replies Latest reply on Nov 13, 2018 6:26 AM by Jim Sweatman

    Reconciling QuickBooks Online data in Tableau Desktop 10.2

    Eric Summers

      I am testing out some sample data in QBO using the built in connector from Tableau. I found variances in my sales by customer report when viewing in Tableau. I traced these back to the following issues:

       

      1. When looking at line_item_amount from the sales expense dtl table it seems to be missing credit memos that were issued against invoices. More specifically, the tax amount of the refund listed in the CM is where the variance lies.

      2. The other items that are causing variances are reimbursable expenses. In November under Brad Lamb I am off by 2300. In QBO I can trace this back to reimbursable expense for services performed. This is in the image attachment. I am looking for the table/measure combinations that will provide the CM tax amounts and the reimbursable expense amount items directly in a sales by customer report. Has anyone come across a similar issue with the QBO connection?

      sales receipt with reimbursable expense-Brad Lamb Nov16.PNG

       

      The Excel sheet contains a report from QBO for customer sales by month. The twbx is what I am trying to reconcile to this QBO report. I have added the tableau amounts (red) to the excel sheet for easier access.

       

       

      Thanks!

      Eric

        • 1. Re: Reconciling QuickBooks Online data in Tableau Desktop 10.2
          Elizabeth Espinoza

          Hi Eric,

           

          Sorry it took me so long to dig into my data. I'm noticing errors with refunds and expense reversals as well. For example, in my account there is a charge for 500, a reversal for 500, and a second charge for 500. In quickbooks, this is showing as a total of $500; however, in Tableau, it's showing up as $1500. I also have an account for discounts and refunds set up as an income account in quicbkooks. Tableau shows the balance of this account as positive, whereas quickbooks shows it as negative. When summing the total income, quickbooks performs this as Sales - Refunds. Tableau does Sales + Refunds.

           

          I think what is going on here is that Tableau is overriding the - signs associated with transaction lines. Which is unfortunate. When I have an entire account that needs to be changed, that's easy enough to do with a calculation in Tableau; but for individual expense reversals, it's likely going to cause a break every time.

          • 2. Re: Reconciling QuickBooks Online data in Tableau Desktop 10.2
            Eric Summers

            Thanks Elizabeth! I am thinking the same thing, that Tableau just isn't handling the small one off accounts and transactions properly. I've tried to reach out to support about this but didn't get anywhere. I was hoping to talk to someone about how the tables visible in the data connection are joined and populated.  Drew Loika

            • 3. Re: Reconciling QuickBooks Online data in Tableau Desktop 10.2
              Drew Loika

              Something here definitely isn't right, thanks for bringing it to my attention, and my apologies if you didn't have a great support experience. The team and I will look into this.

               

              Thank you!

               

              Drew Loika

              Product Manager

              Tableau

              • 4. Re: Reconciling QuickBooks Online data in Tableau Desktop 10.2
                Eric Summers

                Thanks Drew! I was able to reconcile this weekend using a different third party connector so I know it is possible. Let me know if you want to go over the tables I used to accomplish this. I will also be trying to reconcile my third party book to my tableau connected book and see if I can figure it out there.

                 

                Eric

                • 5. Re: Reconciling QuickBooks Online data in Tableau Desktop 10.2
                  Drew Loika

                  Quick update: We've identified the issue and expect to have a fix deployed shortly.

                  • 6. Re: Reconciling QuickBooks Online data in Tableau Desktop 10.2
                    Eric Summers

                    That's great news. Thanks Drew and team Tableau!!

                    • 7. Re: Reconciling QuickBooks Online data in Tableau Desktop 10.2
                      Rishabh Dhingra

                      I can see my expense report in Quickbooks only which shows me the Memo/Description, Transcation Type and expense amount in Quickbooks along with other fields. However, in Tableau I am unable to see the customer/project in the Accounts table and I am unable to see Memo/Description as well. If I look at other tables like Sales and Expenses and the line item detail , there is only one transaction type available for all the projects ., i.e:- Invoice whereas I expect to see Bill , Expense etc Transaction Types as well as shown in the Expense report in Quickbooks.

                       

                      Also, I can see other Transaction Types but they are all against Null Project. I am not too sure if Tableau quickbooks online connector is working fine. Could you please suggest?

                      • 8. Re: Reconciling QuickBooks Online data in Tableau Desktop 10.2
                        Nick Neal

                        Did anyone get resolution on the Quickbooks connector? 10.5 was released a few weeks ago and there appear to be some serious issues with the Quickbooks connector. It's basically unusable. If you are thinking of getting Tableau because of the Quickbooks connector, I would think twice as this seems to not be working correctly and has been in the same state since June 2017, as far as I can tell.

                        • 9. Re: Reconciling QuickBooks Online data in Tableau Desktop 10.2
                          Drew Loika

                          Hey all, thanks for your feedback. Nick, the QuickBooks connector included in 10.5 is the same as earlier versions, and as you note leaves a lot to be desired. We are committed to making this right, so I'd encourage you to try our beta of a brand-new connector that the feedback we've received into account.

                           

                          Please see: Tableau Data Connectivity Pre-Release

                           

                          I would also like to highlight the pre-built content we've created along with the connector, also in beta, that recreates the P&L, among other things. This is available at the above link too. Note that we're still finding and fixing issues so it's not perfect yet but we're fixing problems as fast as we find them. Please give it a try and let us know if there's anything we missed.

                           

                          Best,

                           

                          Drew Loika
                          Product Manager - Cloud
                          Tableau

                          • 10. Re: Reconciling QuickBooks Online data in Tableau Desktop 10.2
                            Jim Sweatman

                            Hey Drew,

                            In an earlier thread to this conversation, you said in June 2017:

                            Drew Loika wrote:

                             

                            Quick update: We've identified the issue and expect to have a fix deployed shortly.

                            I am experiencing the same issue using the Sales and Expenses by Line Item table from the QBO Connector. Below are examples where QBO Connector did not provide the correct sign for some transaction amounts. Examples included Types of Credit Card Credit, Journal Entry, and Deposit. Journal Entry and Deposit can and will contain both positive and negative transactions, so I cannot create general rules to manage this.

                             

                            Is this issue similar to the one that you mentioned correcting in June 2017?

                            Tableau Community - 608778 - Examples 1 and 2.png

                            Tableau Community - 608778 - Example3.png

                            Tableau Community - 608778 - Example 4.png