Skip to content
+1-888-319-3663

COMMUNITY FORUM

date calculation

Toni asked 7 years ago
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???
Answers
Best Answer
Jared answered 7 years ago
Hi Toni,
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
Toni replied 7 years ago

Thanks. Tried #1. In SmartList I selected the date range between 01/01/2013 and 12/31/2014, otherwise I don’t get data from 2013. The result for January 2013 is correct, but in 2014 all the data from 2013 are also included in every month.


If you would like to submit an answer or comment, please sign in to the eOne portal.