topic Re: Earliest Date question in QlikView

Earliest Date question

Fri, 11 Nov 2011 15:36:10 GMT

Hi,

How can I find out the earliest date in the data?

I have a date field which I converted in mm/dd/yyyy format in the load script. This date represents when a ticket was created. How can I find out in my expression which is the earliest date a ticket was created. Is there a function like max(Date) which I know is used to find the latest date, but I want to go the other extreme (i.e. the earliest date).

So, if the date field is CreatedDate, is there a function like min(CreatedDate) or any other workaround for this scenario.

Thanks for your help.

Re: Earliest Date question

Fri, 11 Nov 2011 17:49:13 GMT

Amitesh,

Since data is stored as numeric field, numeric group function will work, but they will return number which you need to convert to data if you wish using it for conditions.

date(max(date))

Kiran.

Earliest Date question

Anonymous — Mon, 14 Nov 2011 14:25:05 GMT

If max(CreatedDate) works fine, min(CreatedDate) should also work.

Try

=min(CreatedDate)

in a textobject then you can see what it returns

Earliest Date question

Tue, 15 Nov 2011 09:26:56 GMT

you can do one thing:

1.first load the data in ascending order of created date:

load * from tablename order by created date asc;

2. create a variable in which you fetch the minimum and maximum value:

let mindate = peek('createdDate',0,'tablename');

let maxdate = peek('createdDate',-1,'tablename');

when you load the data in ascending order then your first value is minimum and last value is maximum.

hope it will help.

regards

Re: Earliest Date question

Wed, 16 Nov 2011 15:30:02 GMT

Sorry for not updating status on this guys, been busy with a ton of other stuff, I did try

max(CreatedDate) and min(CreatedDate) but it did not work.

thanks for your responses.

Earliest Date question

Wed, 16 Nov 2011 15:54:54 GMT

try

Aggr(Min(CreatedDate),Ticket)

Re: Earliest Date question

Wed, 16 Nov 2011 16:04:42 GMT

For these to work, CreatedDate field should be either numeric, date or timestamp. Please check it.

Make sure that your DateFormat in document and the field format are the same.

Kiran.

Re: Earliest Date question

kamalqlik — Thu, 17 Nov 2011 05:00:42 GMT

I think you are not using the right format for date....