Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Earliest Date question

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.

AM

7 Replies
Not applicable

Re: Earliest Date question

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.

dennisnet
Valued Contributor III

Earliest Date question

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

Try

=min(CreatedDate)

in a textobject then you can see what it returns

Not applicable

Earliest Date question

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

Not applicable

Re: Earliest Date question

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.

AM

Not applicable

Earliest Date question

try

Aggr(Min(CreatedDate),Ticket)

Not applicable

Re: Earliest Date question

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.

kamalqlik
Valued Contributor

Re: Earliest Date question

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