cancel
Showing results for 
Search instead for 
Did you mean: 

Calculating an earliest date column within a query.

Former Member
0 Kudos
86

Hello all,

I'm trying to create a column in a query to look across other columns in a query and tell me the earliest date. It's a qualifications expiry report. There are 5 qualifications hard coded into the query. The expiry date is a calculated key figure. I've tried using MIN ( , ) but this only works for two qualifications and if the date is blank, it's treats this the a minimum date. I've also tried using an IF statement and nesting them but this returns numbers rather than a date, even if I use 'Process Value as Date'. Essentially I need the column so that I can sort on it as the report will generate about 6000 rows.

Example Date 1 01.05.2005

Date 2 01.06.2005

Date 3 01.07.2005

I would like to see 01.05.2005 in a new cell called date 4

Any help would be much appreciated.

Thanks.

James

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

James,

Create a forumla variable with replacement path pointing to the key value of each column. Compare these using IF conditions. Once you have the lowest one use the date() function to convert it to a date.

Not sure if this will work but give it a try.

Cheers

Aneesh

Answers (0)