Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Create first report

Hi, all!

I'm totally new to Qlik/QuickBooks Advanced Reporting.  I've gone through the beginning tutorial, but feel like I need to create a couple reports/graphs before I continue with the tutorials for me to get the most out of them.  I'm hoping maybe you can help me create a very basic bar graph so that I can start understanding how to work with expressions, dimensions, etc.

Our chart of accounts for income includes a couple major categories, one of which is Product Sales, and then under Product Sales, in our chart of accounts, we have about 10 sub-accounts for each of the major product lines. 

How would I create a bar chart that would show, in $s, the revenue for each of the 10 Product Sales sub-accounts for the YTD through the last month, please?  So a bar for each of the 10 sub-accounts.  I

Thank you very much for any help you can provide to get me started with using Qlik to do Advanced Reporting in QuickBooks.

Mary

7 Replies
effinty2112
Master
Master

Hi Mary,

First things first, have you written a load script that gets the data from QuickBooks into QlikView? I'll assume you have and this is the simplest way I can think of to get your chart.

Create your bar chart, suggest in the style tab you pick horizontal style. This can be very effective when you sort from largest values at the top to the lowest (in the sort tab select sort by Y-value). Also the horizontal orientation makes it easy to accommodate labels and data values should you want to show these.

For your dimension choose your sub-account field, for your expression Sum(Sales).

At some point in the near future you'll start to create master calendar tables to make it easy to select time periods but for now create a listbox for Date and in the listbox search box type >=01/01/2017 and hit enter.

Add another listbox for your Product Lines, now you can see the sales by sub-account for whatever product line or combination of product lines you wish.

Good luck

Andrew

Anonymous
Not applicable
Author

Hi

Post sample data

Anonymous
Not applicable
Author

Hi, Andrew!

Thank you so much for your help with this!  I won't be back in the QuickBooks file for a couple days, but will look forward to implementing your suggestions. 

I'm not sure about the load script to get the data from QuickBooks into QlikView, but QuickBooks Advanced Reporting has been used in the past by others with this QuickBooks file, and when I've looked at Advanced Reporting with this QuickBooks file, it appears that some Lists, etc. have already been set up.  So I'm optimistically thinking I'm OK there.

I'll respond back when I've had a chance to get into the file and have tried to create the bar chart.

Thanks, again!

Mary

Anonymous
Not applicable
Author

Hi!

Thanks for your response!

What I'd like to do is create a bar chart of certain selected accounts on the QuickBooks Profit and Loss statement.

Here would be an example:

What I'd like to do is ignore the service and shipping revenue/sales, and just have a bar chart showing dollars on one axis and each of the Product Sales accounts on the other axis, with a bar for each Product Sales account reflecting the total revenue Jan. - August.  So there would be 4 bars on the chart, one each for the total sales for Widget A, B, C & D.

  

  

Profit and Loss Statement From QuickBooks
Jan. - August
#400 Product Sales
#401 Widget A Sales $                       5,000.00
#402 Widget B Sales $                       7,000.00
#403 Widget C Sales $                           200.00
#404 Widget D Sales $                     12,000.00
Total Product Sales $                     24,200.00
#410 Service Sales
#411 Consultation Sales $                           500.00
#412 Training Sales $                           250.00
Total Service Sales $                           750.00
#420 Shipping and Handling Revenue $                           180.00
Total Sales $                     25,130.00

Thank you for any suggestions you have!

Mary

Anonymous
Not applicable
Author

Hi

Are you expecting like this

Community.JPG

Anonymous
Not applicable
Author

Hi!

Thanks for your reply and sample graph!  Yes - that's exactly what I'm hoping to create.

Any tips you can provide on how to create that graph would be greatly appreciated!

Mary

Anonymous
Not applicable
Author

Hi

Please find attached application.

Hope it helps!! !