First record

[Krish2459_58]

Hi,

I need to show the immeditdate first record which is greater than today() in a KPI box.

In the below example I want to show the highlighted one(0 Years 5 months 11 days) in KPI box.

Logic to derive "Years until next Review"

if(num([review date])-$(vTodaysDateNum)<0,null(),
div(num([review date])-$(vTodaysDateNum),365) & ' Years ' &
div(mod(num([review date])-$(vTodaysDateNum),365),30) & ' months ' &
mod(mod(num([review date])-$(vTodaysDateNum),365),30) & ' days') as "Years until next Review"

Thanks..

[Krish2459_58]

any suggestions please.

[Vegar · Accepted solution]

By first record you mean the record with the shortest lowest  "Years until next Review" value.

You could try just using the following expression

=MinString("Years until next Review")

[marcus_sommer]

You need to add the condition within the where-clause, maybe with something like:

where MyCalculation >= 0 and MyCalculation <= 365;

and MyCalculation might be just: MyDate - today().

Beside this I could imagine that using a simple interval() formatting instead of the complex string-concatenation also returned the wanted period-information.

[Abney_542]

thanks for sharing