Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Showing results for
Did you mean:
Not applicable

## Finding Minimim Date within a record

I have several thousand records and there are three actions different our users can take and mark the date actioned in the relevant field in our core systems, and at least one of them needs to be done by a required date.

I need to build a report that evaluates the three fields to establish if there are one or more dates then at least one or more of them are before the required date.

I want to know that if Date A or Date B or Date C has a value in it, what is the minimum date of the entered ones.

I have tried using the Min function but it looks at the database when I want to look in each record (and create a new field holding this minimum date or blank if no dates are entered).

I have tried nested IF's with If Date A < Date B (using the output to create a new field) etc but it is unweildy and the blanks appear to confuse the calculation.

In excel I simply use =min(DateA,DateB,DateC) in a new column.

1 Solution

Accepted Solutions
Luminary Alumni

Hi,

Try RangeMin(Field1, Field2, Field3, .......), to get minimum of the values.

Hope this helps you.

Regards,

Jagan.

6 Replies

Hi,

Do you mean you need a minimum date of three date fields?

If yes, you can try something like this.

Data:

Load * from data.qvd;

Min_Temp:

Load min(DateField1) as Min resident Data;

Load min(DateField2) as Min resident Data;

Load min(DateField3) as Min resident Data;

Min:

Load min(Min) as MinimumDate resident Min_Temp;

Drop table Min_Temp;

Now you will have a minimum date from all three date fields.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Luminary Alumni

Hi,

Try RangeMin(), to get minimum of the values.

Hope this helps you.

Regards,

Jagan.

Not applicable
Author

Not quite, I want the minimum date from three date fields for each record in the data base.

So record number 1 might have 3 dates, I need the Minimum,

record 2 might have 1 date, I need the minimum (which will be that date)

record 3 might have no dates so I will get a blank

record 4 might have 2 dates, I need the minimum

etc

I can then evaluate for each record whether they managed to action it in time bearing in mind they have three different options and may take all three options, one or not done anything....

Luminary Alumni

Hi,

Try RangeMin(Field1, Field2, Field3, .......), to get minimum of the values.

Hope this helps you.

Regards,

Jagan.

Hi,

Ok so you can try this way.

if(date1 < date2, if(date1< date3, date1, date3),if(date2 < date3,Date2,date3)) as min date

from xyx

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable
Author

Thank you very much for the responses. I have used the MinRange solution and it works but other items also help with my development and learning in this product.

Thank you very much indeed.

Community Browser