Sales Analysis Query

[larryenet]

Hello SAP Gurus,

Thanks in advance for any help!!

Can someone help me with fixing a couple lines I added to do the following:

'Sales Amount' - 'GrossProfit' / 'Quantity' or the same as Item Cost.

This is what I added to below to be make it short:

, a.LineTotal- (a.LineTotal*isnull(b.DiscPrcnt,0)/100)-(a.GrssProfit/a.Quantity) 'Item Cost'
Union all
, -a.LineTotal- (a.LineTotal*isnull(b.DiscPrcnt,0)/100)-(a.GrssProfit/a.Quantity) 'Item Cost'

;WITH Sales as


(


select 'ARInvoice'[Type], a.Docentry , b.docnum as 'InvoiceNo', b.docdate as 'InvoiceDt'


, b.NumAtCard as 'Purchase Order No.'


, b.cardcode as 'Customercode', b.cardname as 'Customername'


, a.ItemCode, a.Quantity


, case when b.DocType = 'I' then 'Item' Else 'Service' End 'Doctype'


, a.WhsCode , c.SlpName as 'Sales Employee'


, a.Price


, a.LineTotal- (a.LineTotal*isnull(b.DiscPrcnt,0)/100) 'SalesAmount' 


, a.GrssProfit as 'Grossprofit'


, a.LineTotal- (a.LineTotal*isnull(b.DiscPrcnt,0)/100)-(a.GrssProfit/a.Quantity) 'Item Cost'


from INV1 a


inner join OINV b on a.DocEntry = b.DocEntry and b.Canceled ='N'


inner join OSLP c on c.SlpCode = b.SlpCode


where a.DocDate >= [%0]


and a.DocDate <= [%1]


Union All


select 'ARCreditMemo'[Type], a.Docentry , b.docnum as 'InvoiceNo', b.docdate as 'InvoiceDt'


, b.NumAtCard as 'Purchase Order No.'


, b.cardcode as 'Customercode', b.cardname as 'Customername'


, a.ItemCode, a.Quantity


, case when b.DocType = 'I' then 'Item' Else 'Service' End 'Doctype'


, a.WhsCode , c.SlpName as 'Sales Employee'


, a.Price


, -a.LineTotal- (a.LineTotal*isnull(b.DiscPrcnt,0)/100) 'SalesAmount' 


, -a.GrssProfit as 'Grossprofit'


, -a.LineTotal- (a.LineTotal*isnull(b.DiscPrcnt,0)/100)-(a.GrssProfit/a.Quantity) 'Item Cost'


from RIN1 a


inner join ORIN b on a.DocEntry = b.DocEntry and b.Canceled ='N'


inner join OSLP c on c.SlpCode = b.SlpCode


where a.DocDate >= [%0]


and a.DocDate <= [%1]


)


Select * ,


Case When SalesAmount <>0 and Type = 'ARInvoice' then GrossProfit / SalesAmount*100


  When SalesAmount <>0 and Type = 'ARCreditMemo' then -(GrossProfit / SalesAmount*100)


  When SalesAmount = 0 Then 0 end [GP%]


from Sales

[larryenet · Accepted solution]

Thank you very much Johan!! You da man!!

Hi Larry,

Item Cost (each) / Cost of Item

a.GrossBuyPr

Extended Item Cost / Cost of Line

a.GrossBuyPr * a.Quantity

Regards,

Johan

[larryenet]

Basically I need these two columns added to the query:

'SalesAmount' - 'Grossprofit' / a.Quantity = 'Item Cost (Each)'

'Item Cost (Each)' * a.quantity = 'Extended Item Cost'

This code did not work after all. The numbers aren't correct. Can you please add Item Cost (each) and Extended Item Cost to the query? Thanks again for all of your help with this.

, a.LineTotal- (a.LineTotal*isnull(b.DiscPrcnt,0)/100)-(a.GrssProfit /CASEWHEN a.Quantity <=0THEN1ELSE a.Quantity END)'Item Cost'
,-a.LineTotal- (a.LineTotal*isnull(b.DiscPrcnt,0)/100)-(a.GrssProfit /CASEWHEN a.Quantity <=0THEN1ELSE a.Quantity END)'Item Cost'

[Johan_H]

Hi Larry,

Looking at the lines, that would mean that a.Quantity can be zero.

You can catch this with a CASE clause:

, a.LineTotal- (a.LineTotal*isnull(b.DiscPrcnt,0) / 100)-(a.GrssProfit / CASE WHEN a.Quantity <= 0 THEN 1 ELSE a.Quantity END)'Item Cost'
,-a.LineTotal- (a.LineTotal*isnull(b.DiscPrcnt,0) / 100)-(a.GrssProfit / CASE WHEN a.Quantity <= 0 THEN 1 ELSE a.Quantity END)'Item Cost'

Regards,

Johan

P.S. Please use the comment function below this answer to reply, otherwise I will not be notified of any response.

[larryenet]

Hi Johan,

This worked for one of the columns I needed, but I was trying to get Item Cost and Extended Item Cost.

The lines you gave me show Extended Item Cost. Can you change the line so that it will show Item Cost (each)?

sales amount - GP / Qty

Thank you very much for your help!!

Kind regards,

Larry

[Johan_H]

Hi Larry,

I am not sure what you need, because as far as I can tell the sql code you provided is doing exactly what you are asking:

/* sales amount - GP / Qty */
, a.LineTotal- (a.LineTotal*isnull(b.DiscPrcnt,0)/100) /* <- sales amount */
  - /* <- minus */
 (a.GrssProfit /* <- GP */
  / /* <- divided by */
  CASE
   WHEN a.Quantity <= 0 THEN 1
   ELSE a.Quantity END) /* <- Qty */
AS [Item Cost]

So if this code line gives you, what you call 'Extended item Cost', then what is your definition of 'Item Cost', or vice versa, what is your definition of 'Extended item Cost'?

Regards,

Johan

[Johan_H]

Hi Larry,

Item Cost (each) / Cost of Item

a.GrossBuyPr

Extended Item Cost / Cost of Line

a.GrossBuyPr * a.Quantity

Regards,

Johan

[larryenet]

Hi Johan,

I get an error stating "Divide by zero error encountered".

If you remove two lines from the query, it works fine. I tried adding the following lines so that the query would also show Item Cost calculated as follows: 'Sales Amount' MINUS 'GrossProfit' DIVIDED BY 'Quantity' = Item Cost

Here's the two lines I added, but I wasn't sure if it was correct.

, a.LineTotal- (a.LineTotal*isnull(b.DiscPrcnt,0)/100)-(a.GrssProfit/a.Quantity)'Item Cost'
,-a.LineTotal- (a.LineTotal*isnull(b.DiscPrcnt,0)/100)-(a.GrssProfit/a.Quantity)'Item Cost'

Thank you for your help.

Kind regards,

Larry

[Johan_H]

Hi Larry,

We can probably help you fix the query, but it would be a lot easier if you could tell us what is wrong with it. Do you get an error, or is it not returning the expected result?

Regards,

Johan