Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Running into an issue creating a job profitability and margin report by customer. I am using Quickbooks Advanced Reporting and followed this guide: QuickBooks Advanced Reporting – Building A Report - insightfulaccountant.com
My dimension is Transactions.Customer Full Name
My expressions are for sales revenue, cost, profit, profit margin, and sales rep. Everything looks good except for the cost column. This is the definition I am using: sum({<$(vExprCogs)>}[Transactions.Amount With Sign])
My cost column is filling in all 0's.
I also am trying to figure out how to have that column show not only COGS associated with that job but also payroll expenses associated with that job.
Appreciate any help, and please talk to me as if I know nothing about what I am doing.
How is your set analysis variable defined? This guy -> vExprCogs
It is not defined. What you see is exactly what I have entered.
But what is vExprCogs? This isn't a field in your script, is it?
Kathryn,
If you go to your Settings|Variable Overview from you drop down menu you should see a list of variables.
If you select the variable it will show you the definition of the variable itself.
Good luck
Oscar
I'm not sure if this is what you are asking, but when I go to the variables tab of the Edit Expression screen and select vExprCogs from the dropdown box, this is what it says in a text field underneath:
[Transactions.Account Type]={'Cost of Goods Sold'}, Transactions.Pending={0}, [Transactions.Txn Type]={'Invoice','Sales Receipt','Credit Memo', 'Charge', 'ARRefundCreditCard'},[Transactions.Inventory Table]={1}
I believe the QBAR interface is a little different from the standard Qlikview.
I think this is what you are asking for.
[Transactions.Account Type]={'Cost of Goods Sold'}, Transactions.Pending={0}, [Transactions.Txn Type]={'Invoice','Sales Receipt','Credit Memo', 'Charge', 'ARRefundCreditCard'},[Transactions.Inventory Table]={1}
Yes, that is what I meant to know. But I don't see anything wrong here... are you able to share a sample to check it out?
Kathryn,
Without looking at your data it is difficult to break down the solution.
That being said, I would trouble shoot the situation by breaking down the expression into parts:
First of all your original expression is using a variable for the set analysis filters.
sum({<$(vExprCogs)>}[Transactions.Amount With Sign])
I would replace the variable with the set analysis filters, something like this:
Sum( {<
[Transactions.Account Type]={'Cost of Goods Sold'},
Transactions.Pending={0},
[Transactions.Txn Type]={'Invoice','Sales Receipt','Credit Memo', 'Charge', 'ARRefundCreditCard'},[Transactions.Inventory Table]={1}
<}
[Transactions.Amount With Sign])
Then I would start attempt to break down the expression into four parts, because you have four filters in your set analysis.
Sum( {<
[Transactions.Account Type]={'Cost of Goods Sold'}
<}
[Transactions.Amount With Sign])
Sum( {<
Transactions.Pending={0}
<}
[Transactions.Amount With Sign])
Sum( {<
[Transactions.Txn Type]={'Invoice','Sales Receipt','Credit Memo', 'Charge', 'ARRefundCreditCard'}
<}
[Transactions.Amount With Sign])
Sum( {<
[Transactions.Inventory Table]={1}
<}
[Transactions.Amount With Sign])
One of these filters is causing the problem, I would also mix and match the filters until I found the problem.
Good luck!
Oscar
I am still not able to fix this. Would you be willing to help via teamviewer or VNC for some PayPal money??