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: 
Not applicable

Please help! COGS showing as 0

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. 

11 Replies
sunny_talwar

How is your set analysis variable defined? This guy -> vExprCogs

Not applicable
Author

It is not defined.  What you see is exactly what I have entered.

sunny_talwar

But what is vExprCogs? This isn't a field in your script, is it?

oscar_ortiz
Partner - Specialist
Partner - Specialist

Kathryn,

If you go to your Settings|Variable Overview from you drop down menu you should see a list of variables.

264642.PNG

If you select the variable it will show you the definition of the variable itself.

Good luck

Oscar

Not applicable
Author

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}

Not applicable
Author

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}

sunny_talwar

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?

oscar_ortiz
Partner - Specialist
Partner - Specialist

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

Not applicable
Author

I am still not able to fix this.  Would you be willing to help via teamviewer or VNC for some PayPal money??