difference between Max date corresponding value and second max corresponding value?

[#1]

Hi All,

I have below data

Engine	Date	Hrs
A	11/3/2014 15:00	15
A	11/3/2014 15:20	15
A	11/2/2014 12:00	10
B	20/1/2014 06:00	20
B	12/2/2014 12:00	25
B	10/3/2014 7:23	30

Formula: Hrs=(max date corresponding hrs-2nd max date corres Hrs) /(max date-2nd max date)

Ex: Engine A=(15-15)/(11/3/2014-11/3/2014)

Engine B=(30-25)/(10/3/2014-12/2/2014)

                =5/28.

Numarator: Hrs difference

Dinaminator: Days difference.

I need to calculate this in back end because in chart i need to show in year and month wise.

Date means service date

[tresesco · Accepted solution]

Straight table:

Dimension: Engine

Expression: =(FirstSortedvalue(Hrs, -Date) -FirstSortedvalue(Hrs, -Date, 2))/ (Max(Date) -max(Date,2))