-
1. Re: Recreating a P&L with Tableau Quickbooks Online connector
diego.medrano Aug 5, 2016 4:30 PM (in response to Taylor Stein)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 Oct 9, 2016 2:03 AM (in response to diego.medrano)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 Oct 11, 2016 8:39 AM (in response to vijay goel)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 Oct 17, 2016 11:20 AM (in response to 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.0Nov 11, 2016 12:46 PM (in response to Katharine Frazier)
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 Nov 14, 2016 7:39 AM (in response to lindy.eberhart.0)2 of 2 people found this helpfulHi 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
-
7. Re: Recreating a P&L with Tableau Quickbooks Online connector
Tony Saudek Feb 15, 2017 1:28 PM (in response to Katharine Frazier)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.
- Rows Shelf:
-
8. Re: Recreating a P&L with Tableau Quickbooks Online connector
Drew LoikaFeb 23, 2017 12:34 PM (in response to Taylor Stein)
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 Apr 4, 2017 12:11 PM (in response to Taylor Stein)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 Apr 5, 2017 4:22 AM (in response to Taylor Stein)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.
-
11. Re: Recreating a P&L with Tableau Quickbooks Online connector
Elizabeth Espinoza Apr 13, 2017 5:49 AM (in response to Taylor Stein)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 Apr 13, 2017 5:49 AM (in response to Katharine Frazier)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 Apr 24, 2017 11:19 PM (in response to Elizabeth Espinoza)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 LoikaApr 26, 2017 4:42 PM (in response to Elizabeth Espinoza)
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