1 2 Previous Next 26 Replies Latest reply on Mar 28, 2018 1:58 PM by Patrick Trainer

    Recreating a P&L with Tableau Quickbooks Online connector

    Taylor Stein

      I'm new to Tableau but not to Quickbooks. I'm trying to simply recreate my income statement for a single company but cannot figure out how to join transactions to accounts. Neither of the Sales and Expenses tables seem to have an Account field to identify which Account the transaction goes with. Has anyone been able to recreate their income statements?

       

      Any help or advice at all would be appreciated.

       

      Thanks!

        • 1. Re: Recreating a P&L with Tableau Quickbooks Online connector

          Hey Taylor,

           

          Would you be able to dummy up some data showing what your data looks like and what you're hoping it'll look like?

           

          Also, have you tried the Quickbooks connector in the 10.0 Beta?

          • 2. Re: Recreating a P&L with Tableau Quickbooks Online connector
            vijay goel

            Diego,

            I have the same problem. I think it's a matter of creating a tutorial that highlights which data to pull...it's grouped by the connector and quickbooks into a number of datatables I can't make sense of and don't have a data map for (since it comes from you and quickbooks).

             

            Since you've officially supported the QBO data connector, would be really helpful to show how to generate common reports in Tableau (e.g., profit and loss, cash flow) or create a template us mere mortals can refer back to.

             

            I run a catering company, so we're trying to use Tableau to calculate and track some key operational ratios: kitchen expense vs food revenue, event staff expense vs. event staff revenue, revenue vs. rent/ utilities, etc.  These would be line items I'd pull out of the monthly view of the P&L and we do it today manually by copying the P&L data from the month to month QB P&L report.

             

            Thanks!

            Vijay

            • 3. Re: Recreating a P&L with Tableau Quickbooks Online connector
              Katharine Frazier

              I'm in a similar situation - digging into this today and expect to work on it over the next few weeks. If I have any breakthroughs I'll share them with you here. If you all have found any resources/guides for setting up QB data in Tableau, please share likewise.

               

              Thanks!

               

              Katharine

              • 4. Re: Recreating a P&L with Tableau Quickbooks Online connector
                Katharine Frazier

                I've got to the point where I can manipulate data from QB in various ways. What I'm seeing is validated through reports in QB.

                 

                I cannot figure out how to refresh the data. I understand it is not a live connection, but when I "refresh extracts" it does not update to the current information.

                 

                Any help?

                • 5. Re: Recreating a P&L with Tableau Quickbooks Online connector
                  Lindy Eberhart

                  Hi Katharine,

                   

                  Were you able to build a successful join to create a P&L statement report in Tableau? If so, could you share which tables you joined on how you joined them?

                  • 6. Re: Recreating a P&L with Tableau Quickbooks Online connector
                    Katharine Frazier

                    Hi Lindy,

                     

                    Yes and no...

                    I was able to use the table "Sales and Expenses with Line Item Details" to create a list of accounts and amounts and view by Month, Year, etc...

                    Then I created subgroups to group the data. For the most part, it agreed with our P&L.

                    This worked quite well and was providing the information I needed. However, I have been unable to figure out how to easily refresh the data.

                     

                    I am barely holding my head above water since I am a brand new Tableau user and not accustomed to working with databases. The Quickbooks database is not an easy to one to work with I am finding!

                     

                    I put the Tableau visualization project on hold to focus on creating a live data connection to Quickbooks. I have that now and am about to jump into using Tableau again. Here's what I have done:

                     

                    Using the QODBC database connector (QODBC.com Tools for QuickBooks) I am pullling raw data from Quickbooks into Excel using the Query function. I can now shape the data into something I understand.

                    Now I'm going to connect to those spreadsheets using Tableau and start buildiing what I need. It is not as slick as what I want (something cloud-based, live updating) but it is going to get the job done. I feel like I have more control over the data this way. I'm using latest version of Excel 2016 and the query tool is pretty easy to use.

                     

                    Hope this helps a little...

                     

                    Katharine

                    2 of 2 people found this helpful
                    • 7. Re: Recreating a P&L with Tableau Quickbooks Online connector
                      Tony Saudek

                      I'm struggling with the reverse problem: I am very familiar with Tableau, but new to OQB. I've been asked to recreate some of our core reports from OQB in Tableau, and am having a **** of a time trying to recreate the numbers I see in OQB. Yes to all the problems folks have listed above.

                       

                      I am trying to recreate a modified Profit and Loss table. My challenge is that the fields as they are presented in Tableau have a different naming convention than what is shown in OQB, and no data dictionary to help.

                       

                      I am working on Tableau 10.1.4

                       

                      My success to date: Recreating the generic Sales by Client report in OQB. Here's how I did it:

                      • Rows Shelf:
                        • Customer_Name
                        • Day(Transaction_Date)
                        • Transaction_Type
                        • Line_Item_Account_Name
                        • Line_Item_Description
                      • Measures:
                        • Sum(Line_Item_Quantity)
                        • Sum(Line_Item_Unit_Price)
                        • Sum(Line_Item_Amount)
                      • Filter:
                        • Transaction_Type: Invoice
                      • NOTE: The Balance field seems to have a native LOD type summation at the Customer level. In this report it provided for each line item a value equal to the sum of [Line_Item_Amount] at the Customer level.

                       

                      I have had no such luck re-creating the "Expenses by Vendor Summary" report, "Transaction List by Vendor."  I gave up after that.

                       

                      Bottom line: as of now I can't find the fields I need in the offered fields. I would welcome anyone else who has a breakthrough.

                      • 8. Re: Recreating a P&L with Tableau Quickbooks Online connector
                        Drew Loika

                        Hi all, thanks for all the information and sharing. Tableau worked really hard on the QuickBooks connector but after listening to customer feedback we've learned that we've got a ways left to go. We are currently making significant changes to the connector and also working on recreating the most common/popular QBO reports. As soon as it's ready we'll have the new functionality available for feedback at https://www.tableau.com/getbeta. Thanks for your patience!

                         

                        Drew Loika

                        Product Manager - Cloud

                         

                        Tableau

                        • 9. Re: Recreating a P&L with Tableau Quickbooks Online connector
                          Melissa Palchak

                          I'm a novice to QB and somewhat familiar with Tableau. I have set up the connection, but struggling a bit with the workbook. Does anyone know if the fields are all consistent across QB users? If yes, if anyone would be willing to share their workbook with dummy data, I'd really appreciate it!!

                          • 10. Re: Recreating a P&L with Tableau Quickbooks Online connector
                            Elizabeth Espinoza

                            I haven't verified all of the figures yet, and there does seem to be a small discrepancy, but so far, I've had success connecting my accounts with my transactions. I'm connecting the Accounts table to the Sales and Expenses with Line Item Details on ID from Accounts and Line_Item_Account_ID. Additionally "Fully Qualified Name" in Accounts matches Line_Item_Account_Name in the "Sales Expenses with Line Item Details". As I continue to verify everything, I'll report any discoveries back here. When you're comparing amounts, I found I generally want to use the Current_Balance_with_Sub_Accounts from the Accounts table and Line_Item_Amount from the "Sales and Expenses with Line Item Details" table. I imagine there could be some variation here depending on how one uses quickbooks.

                             

                            Screen Shot 2017-04-05 at 7.18.13 AM.png

                            • 11. Re: Recreating a P&L with Tableau Quickbooks Online connector
                              Elizabeth Espinoza

                              Hi Taylor,

                               

                              If you're still looking into this, I believe I have found the fields that need to be joined in my post above.

                              • 12. Re: Recreating a P&L with Tableau Quickbooks Online connector
                                Elizabeth Espinoza

                                Hi Katharine,

                                 

                                The problem for me was that I need to sign into quickbooks through Tableau for each refresh. I accomplish this by going to Data in the menu, expanding the menu for my quickbooks data source, and clicking edit data source. Once I re-input my credentials, I am able to then go to Data>(Data source)>extract>Refresh. Without that first step, the refresh just times out.

                                 

                                For Tableau developers: it would be nice if Tableau would prompt for sign-in when we go to refresh the extract if we're using Tableau desktop.

                                 

                                Elizabeth

                                • 13. Re: Recreating a P&L with Tableau Quickbooks Online connector
                                  Eric Summers

                                  Hi Elizabeth,

                                   

                                  I just caught your response to a similar question I have. Can you see if the tax amount from Credit Memos is what is causing your small variances?

                                  Reconciling QuickBooks Online data in Tableau Desktop 10.2

                                   

                                   

                                  Thanks!

                                  Eric

                                  • 14. Re: Recreating a P&L with Tableau Quickbooks Online connector
                                    Drew Loika

                                    Elizabeth, thanks for sharing the difficulty you're having signing in, this is not the expected behavior for the connector, it should prompt you when it needs credentials. I've passed this to the team for investigation.

                                     

                                    Best,

                                     

                                    Drew Loika
                                    Product Manager
                                    Tableau

                                    1 2 Previous Next