topic Date Range Category in QlikView

Date Range Category

qlikwiz123 — Sat, 16 Nov 2024 21:51:27 GMT

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.

Re: Date Range Category

dplr-rn — Tue, 20 Nov 2018 17:12:29 GMT

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)

Re: Date Range Category

qlikwiz123 — Tue, 20 Nov 2018 17:49:01 GMT

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

Re: Date Range Category

dplr-rn — Tue, 20 Nov 2018 17:49:42 GMT

Share your script please

Re: Date Range Category

qlikwiz123 — Tue, 20 Nov 2018 17:54:38 GMT

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.

Re: Date Range Category

dplr-rn — Tue, 20 Nov 2018 17:57:42 GMT

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

Re: Date Range Category

qlikwiz123 — Tue, 20 Nov 2018 18:55:58 GMT

PFA

Re: Date Range Category

dplr-rn — Tue, 20 Nov 2018 19:00:41 GMT

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