Sales for customer

jonathanchye
jonathanchye Member Posts: 34
in SQL General
Hi all,

I'm fairly new here so please bear with me :)

I'm writing TSQL for simple report which shows Sales (LCY) by customer. As a reference I'm using the Customer Statistics window to check if my values are correct.

Initially I went with the Value Entry table by summing the Sales Amount (Actual) column. The reason is the report also includes other fields for the report such as Item No. This method shows the correct Sales figure for most customers except some.

I've then replicated in TSQL the same logic as the Sales (LCY) flow field on the Customer table (summing the Sales (LCY) column from the Cust Ledger Entry table) which as expected consistently gives me the same figure as on the NAV window.

My question is why are the sales values different for some customers? Am I missing anything obvious here?

Below is a stripped down TSQL code I've used for testing. The results from Value Entry table seems to be less than expected.

select sum([Sales Amount (Actual)]) from [MYCOMPANY].[dbo].[MYCOMPANY$Value Entry]
where [Source No_] = '8127743'
and year([Posting Date]) = 2015

select sum([Sales (LCY)]) from [MYCOMPANY].[dbo].[MYCOMPANY$Cust_ Ledger Entry]
where [Customer No_] = '8127743'
and year([Posting Date]) = 2015

Best Answers

Answers

  • jonathanchye
    jonathanchye Member Posts: 34
    Thanks Jen! That really clears it up. I will have to clarify with the company if they are fine to only see item only sales figures as I have created stored procedures and data warehouse tables based on Value Entries.
    If not I guess it's not too much of a problem to change to Customer Ledger Entries. I will just have to figure out method to also include item level information in the report. :)
  • jonathanchye
    jonathanchye Member Posts: 34
    Apologies, one more question, is the Sales Amount (Actual) column in Value Entries table in local currency?

Categories