Date Range Category

[qlikwiz123]

I have a date field DateA with dates in the format 11/10/2018 12:00:00 AM.

I need to create Date Range Bucket in the script showing

Today, Recent Date (Maximum Date from the DateA), Last week, Last Month, Last Year.

How do I create this bucket? FYI, the DateA field may or may not have current date, meaning it may or may not contain today's date. In that case, the Maximum Date in the filed becomes my Recent Date.

[dplr-rn]

you can create flags. using something like below

temp:
LOAD 
     Date(max(Date)) as MaxDate
FROM
Dummy.xlsx
(ooxml, embedded labels, table is Sheet1);

Let vSetDate=Date(Floor(FileTime('Dummy.xlsx')));
dummy:
load 	
		if(Date=today(),'Y','N') as TodayFlag
		,if(Date=Date(Peek('MaxDate',0,'temp')),'Y','N') as MaxDateFlag
		,if( (Week(today())-Week(Date))=1
			,'Y'
			,'N') as LastWeekFlag
		, if( (Month(today())-Month(Date))=1
			,'Y'
			,'N') as LastMonthFlag
	,*;
LOAD 
	Test, 
     Date,
     null() as DummyField
FROM
Dummy.xlsx
(ooxml, embedded labels, table is Sheet1)

[qlikwiz123]

Max function in Script throws an error. Says 'Invalid Expression'

[dplr-rn]

Share your script please

[qlikwiz123]

Hi,

Sorry but due to the security of the data, I cannot post the script here. But I tried using a simple excel and Date(Max(Date) )always throws Invalid Expression Error.

[dplr-rn]

Share the one giving the error i.e. the excel one.
most likely check the date field name

[qlikwiz123]

PFA

[dplr-rn]

You should not use id in max load. see below
Load
//ID, remove this
Date(max(Date)) as MaxDate
Resident Data;