Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
RutiTaumanRubin
Creator
Creator

Indicate date field that greater to max date minus 1 month

Hi All

 

I want to create new field in the script that will populate with value 1 if the date is greater that max(date) minus 1 month.

I try the following but its not working:

IF(MAX(Addmonths(date(date#(ACTION_DATE,'YYYYMMDD')),-1))>=date(date#(ACTION_DATE,'YYYYMMDD')),1,0)

 

Could you please advise?

Thanks!!

1 Solution

Accepted Solutions
sunny_talwar

Max date will have to come from a resident load or a mapping load... it can't be used in the non-aggregated table... do something like this

MainTable:
LOAD Date(Date#(ACTION_DATE,'YYYYMMDD')) as ACTION_DATE, other_fields
FROM ...;

Left Join (MainTable)
LOAD Max(ACTION_DATE) as MaxDate
Resident MainTable;

FinalTable:
LOAD *, If(AddMonths(MaxDate, -1) > ACTION_DATE, 1, 0) as Flag
Resident MainTable;

DROP Table MainTable;

View solution in original post

1 Reply
sunny_talwar

Max date will have to come from a resident load or a mapping load... it can't be used in the non-aggregated table... do something like this

MainTable:
LOAD Date(Date#(ACTION_DATE,'YYYYMMDD')) as ACTION_DATE, other_fields
FROM ...;

Left Join (MainTable)
LOAD Max(ACTION_DATE) as MaxDate
Resident MainTable;

FinalTable:
LOAD *, If(AddMonths(MaxDate, -1) > ACTION_DATE, 1, 0) as Flag
Resident MainTable;

DROP Table MainTable;