Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Below expression is not working...how can I put it together ?
SUM(IF(StartdateYear = year(Today()) ,
IF(EndtdateYear = YEAR(Today()),(ExpenseAmountLocal * NoOfMonths)) ,
(Sum(if(StartdateYear=year(Today()),
ExpenseAmountLocal*NoOfMonthsCY))
+
Sum(if(EndtdateYear=year(Today()),
ExpenseAmountLocal*NoOfMonthsCY)))))
Basically what I am looking for is if startyear and Endyear has same year (filter to currentyear)
then (ExpenseAmountLocal * NoOfMonths) else
if startyear and Endyear has different months filter to curentyear for both startyear and Endyear then (ExpenseAmountLocal*NoOfMonthsCY)
Thank you very much.
Not sure I understand fully... would you be able to show by means of an example?
Hi Ansh,
Have you tried moving the SUM to the fields individually?
Something like
=IF(StartdateYear = year(Today()) ,
IF(EndtdateYear = YEAR(Today()),(sum(ExpenseAmountLocal) * sum(NoOfMonths))) ,
if(StartdateYear=year(Today()),
sum(ExpenseAmountLocal)*sum(NoOfMonthsCY)))
+
if(EndtdateYear=year(Today()),
sum(ExpenseAmountLocal)*sum(NoOfMonthsCY))
can u try tis way,
=SUM(IF(StartdateYear = year(Today()) and EndtdateYear = YEAR(Today()),(ExpenseAmountLocal * NoOfMonths)) ,ExpenseAmountLocal*NoOfMonthsCY)
since you are looking for Similar Month-year or Different Month-Year
I think you should work on Month-year field , not on only "Year "
Thank you Sunny and Shiva...I used below expression and looks like it is working so far ,but have to review with the team yet....I have set StartdateCY and EnddateCY to current year in script itself.
sum(if(StartdateCY=EnddateCY,ExpenseAmountLocal*NoOfMonths,
if(StartdateYear=year(today()),ExpenseAmountLocal*NoOfMonthsCY,
if(EndtdateYear=year(today()),ExpenseAmountLocal*NoOfMonthsCY))))