Last month and 6th month flag for sales in script

[rkpatelqlikview]

Hi Team,

The data will update every month end.
Here i need to generate 2 flags in back end. one is Last month and another is Last 6th month Sales.
For that i created below flag in script.

IF(Month(Date(Date))=Month(Today())-6,'Y') as Last_6th_Month,
IF(Month(Date(Date))=Month(Today())-1,'Y') as Last_Month

My script worked till last month in 2019 but it is not working from this year.
Because my condition is based on Today() and at that there is only one year data 2019.
How to resolve this issue to get sales last month and Last 6th month.

[lorenzoconforti · Accepted solution]

Your expression is not working because you are only working with the month number (not month-year) so when you subtract 6 from today you get -5. 

Month(Today) = Jan/1 

Month(Today) - 6 = -5

You need to implement it differently:

IF(MonthStart(Date(Date))= AddMonths( MonthStart(today()),-6) ,'Y') as Last_6th_Month,
IF(MonthStart(Date(Date))=AddMonths( MonthStart(today()),-1),'Y') as Last_Month

[rkpatelqlikview · Accepted solution]

Thanks Loren,

Done this.

IF(Floor(Date(Date))=Floor(Addmonths(MonthEnd(Today()),-6)),'Y') as FFC_6th_Month,

[rkpatelqlikview]

IF(Floor(Date(FFC_Date))=Floor(Addmonths(MonthEnd(Today()),-6)),'Y') as FFC_6th_Month,