SmartList Builder: date calculation current month/same month previous year
I am using this function to get the sales for a certain month: CASE WHEN MONTH ( {Sales Transaction History:Document Date} ) =1 THEN {Sales Transaction Amounts History:Total YTD} ELSE 0 END How can I get the result of the same month from the previous year in another column???
*This post is locked for comments
Have to select date range 1-1-2013 to 12-31-2014 in SmartList, otherwise no result for January prior year. Result of prior year month January is correct, but in current year totals for January 2013 and January 2014 added up together per month and also for the other months.
Here are a couple different ways of doing the calculation to do what you need:
1. CASE WHEN MONTH ( {Sales Transaction History:Document Date} ) = 1 and ((YEAR ( GETDATE() ) - 1) = YEAR ( {Sales Transaction History:Document Date} )) THEN {Sales Transaction Amounts History:Total YTD} ELSE 0 END
2.CASE
WHEN MONTH ( {Sales Transaction History:Document Date})=1 THEN
CASE YEAR (GETDATE() )-1
WHEN YEAR ({Sales Transaction History:Document Date} ) THEN {Sales Transaction Amounts History:Total YTD}
ELSE 0
END
ELSE 0
END
There are a variety of functions through which you can reach your target. Initially, you should calculate the previous month of the previous year as a parameter to be passed for the (case) function. your data set should have either a full date field under one column or two columns (Year and Month)
Now you can proceed with the condition function.
CASE {Table1:Field1}
WHEN value1 THEN {Table1:Field1} + {Table1:Field2}
WHEN value2 THEN {Table1:Field1} + {Table1:Field3}
ELSE {Table1:Field1} + {Table1:Field4}
END
As a reference for your task, you can refer to very useful support article from Microsoft on a list of functions that can be used with Smart List builder
To save yourself a lot of headache, you can create a custome SQL view on the database level, to be published to Dynamics GP without further manipulation on the Smart List builder. You can make use of Victoria Yudin SQL view SQL view for SOP sales by customer by month
Your feedback is highly appreciated,
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,280
Super User 2024 Season 2
#2
Martin Dráb
230,214
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (