Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Last month and 6th month flag for sales in script


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.

last6thmonth.PNG

 

 

 

Labels (2)
2 Solutions

Accepted Solutions
Highlighted
Specialist II
Specialist II

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

 

View solution in original post

Highlighted
Creator III
Creator III

Thanks Loren,

Done this.

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

View solution in original post

3 Replies
Highlighted
Specialist II
Specialist II

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

 

View solution in original post

Highlighted
Creator III
Creator III

Thanks Loren,

Done this.

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

View solution in original post

Highlighted
Creator III
Creator III

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