Sales Last Year PTD

unknown

Our VP of Sales wants to be able to compare a customers sales Period to Date(PTD) to last years PTD. On the Customers form, Payment History tab there is a Sale PTD field but NOT one for Last Year Sales PTD. I have extended the IDO and added a derived field for this, but I need some help with the SQL statement to use. I am not sure how to reference the current period. Any help would be appreciated!

Find more posts tagged with

General Discussions

Accepted answers

All comments

unknown

You can use getdate() function to retrieve current date. YEAR(getdate()) will give you the current year.

unknown

So I think your issue is going to be that SyteLine doesn't keep the PTD info. Once you run month end and year end procedures, those fields get zeroed out for the next period. In your custom report procedure, you would have to calculate those values based on the measure you want to use (Total Price from COs, Customer Shipments, or Invoices) and based on the period parameters passed into the report. E.G.

SELECT

cust_num

,SUM((price * qty_shipped)) AS sum_ext_price

FROM

co_ship

GROUP BY

cust_num

WHERE

ship_date BETWEEN dbo.GetMidnightOf(@PeriodStartDate) AND dbo.DayEndOf(@PeriodEndDate)

For what you want will be more complicated, because you'll need inline/temp tables to store the values for this year's data and the previous year's data. Then you will have a SELECT query at the end that joins the two periods of data together and return the result set to the report.

-Tim

unknown

Here's two methods to get the current period. One uses the first open accounting period, and the second uses the current date to compare to the begin and end dates of your financial calendar periods.

The queries are based on Visual Manufacturing, but you should be able to convert them to the CloudSuite Industrial equivalent.

Regards,

John Keegan

---------------------------------------------------------------------

-- Based on the earliest open accounting period:

select top 1

per.ACCT_YEAR as CURR_ACCT_YEAR

,per.ACCT_PERIOD as CURR_ACCT_PERIOD

from ACCOUNT_PERIOD per

where per.STATUS = 'A'

and per.SITE_ID = '001'

order by per.ACCT_YEAR

,per.ACCT_PERIOD

---------------------------------------------------------------------

-- Or, based on the current date:

select

per.ACCT_YEAR as CURR_ACCT_YEAR

,per.ACCT_PERIOD as CURR_ACCT_PERIOD

from ACCOUNT_PERIOD per

where getdate() between per.BEGIN_DATE and per.END_DATE

and per.SITE_ID = '001'

---------------------------------------------------------------------

Copyright © 2025 Infor. All rights reserved.