I have created a Smartlist that gives me the sales per customer per Months. How can I get the sales in the same report for the same months but from the previous year?
*This post is locked for comments
Hi,
Like Jeff and Mahmoud suggested, a view is probably your best bet. Routinely, however, I have to create SmartLists I can send to other people, so I need to create it inside SLB. Below is an example of the SQL code that you could use in your calculated field to find the first day in July of the previous year:
select DATEADD(m, 6, DATEADD(yy, DATEDIFF (yy, 0, DATEADD(yy, -1, GETDATE())), 0))
Replace 'GETDATE' with your data field. Use your date field, parse the month out of it and then subtract one from the resulting 'month' number.
Change the '6' as follows to get the different months.
January = 0
February = 1
March = 2
April = 3
May = 4
June = 5
July = 6
August = 7
September = 8
October = 9
November = 10
December = 11
Kind regards,
Leslie
To me, the best option is to create your view on the SQL management studio taking into consideration all the required data you want, then get the view published to Smart list builder as mentioned above by Mr. Jeff.
Although, you can still use a various functions in Smart list builder to manipulate the data as requested. A list of functions (including Date functions) is illustrated in the knowledge base article below;
In addition, you may refer to the article published by Dynamics GP Support and Services blog on the link below;
Calculated Fields in Extender and the Builders
Hope this helps out in your inquiry,
You could make a SQL view and base your SmartList on the view.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (