I have to create a Bond Maturing report.
It has the following information:
BONDS MATURING Dates
2013/12/25
2014/12/25
2015/12/25
2016/12/25
2017/12/25

How do I get the Average Maturing Date in this format
Average Maturity:
3 Years, 9 Month

This message has been edited. Last edited by: <Kathryn Henning>,

Calculate the number of months for each payment from now till the payment date.
That will give you 5 numbers from 9 months till 57 months ..together that will be about 165 months. Divided by 5 is 33 as average and that is 2 years and 9 months....so your 3 years and 9 months is wrong.

Thanks Frankie,

I did
SUM
AVE.MATURITY_DATE

and then sliced it from there.

You can not calculate the average of dates.
It is like calculating the average of Amsterdam, Zurich , Milan, London and Oslo
What would be the answer?

What you can do with those towns is ask what the distance is from let's say Paris to all those cities and then calculate the average of that distances.

You gave me 5 dates and all I could do is calculate the number of months from now to those dates and either in days or months calculate the average of that number of days.

Frank,

This got me the result I was looking for using the CAR file with AVE.YYMD

SET ASNAMES = ON
DEFINE FILE CAR
MATURITY_DATE/YYMD = IF COUNTRY EQ 'ENGLAND' THEN 20131225 ELSE
IF COUNTRY EQ 'FRANCE' THEN 20141225 ELSE
IF COUNTRY EQ 'ITALY' THEN 20151225 ELSE
IF COUNTRY EQ 'JAPAN' THEN 20161225 ELSE
IF COUNTRY EQ 'W GERMANY' THEN 20171225 ELSE 20201225;
END
TABLE FILE CAR
SUM
FST.MATURITY_DATE
BY COUNTRY
ON TABLE HOLD AS TEMP1
END

TABLE FILE TEMP1
SUM
AVE.MATURITY_DATE AS 'AVG_MATURITY'
ON TABLE HOLD AS TEMP2
END

DEFINE FILE TEMP2
TODAY /YYMD = &YYMD;
CURR_MONTH /M = TODAY;
AVG_MONTH /M = AVG_MATURITY;
AVG_REMAINING_YEAR/I8 = DATEDIF(TODAY, AVG_MATURITY, 'Y');

END

TABLE FILE TEMP2
PRINT
AVG_MATURITY
AVG_REMAINING_YEAR
COMPUTE AVG_REMAIN_MONTH/I2 = AVG_MONTH - CURR_MONTH;
END

AVG_MATURITY
2015/12/25
AVG_REMAINING_YEAR
2
AVG_REMAIN_MONTH
9

You're still calculating average date. You can't do that.

For example, the average date of (20130228 + 20130301)/2 = 20130264.5
Indeed, February 64.5...

You can't calculate the average of dates, that number has no meaning. Like Frank says, it's like calculating the average of Amsterdam or the average of Broccoli. Averages work for scalar values where a value twice as large means there's twice as much of it. Dates don't behave like that.

What you mean/need to do is to calculate the average AGE.

So, first calculate the MATURITY_AGE of each bond and then take the average of that.

I think your calculation does not work for all combinations of dates and does not work whenever you run it
I even think if you run it today you get an other result
This month it is for instance the 3th month and if you look at your calculation you will see it is 9-3 and in my opinion that would give 6 and not 9

Try to use your formula first by calculating the number of days from today to all the payment days
It can be done in the first part
Then when you see those numbers it would show something like
270
630
1000
1360
1625
The sum is more or less 1000 days
Then you can add that number of days to your start date

Must be some confusion.

What I meant is once we get the Avg Date either
SUM
AVE.DATE

say Average Date is Dec 25, 2015.

I just need to break it down and say its 2YRS and 9months from today.


Thanks everyone for the response.

Since we are using FOCUS dates, remember that they are stored a the number of days since 1900/12/31. This means that the "average date" will be calculated as the average of those days hence it doesn't matter what is the origin of the dates. So the average of the dates will be the average number of days and then transformed into a date which can be considered as the "average date".

Going back to the program, it can be simplified:

  
SET ASNAMES = ON
DEFINE FILE CAR
MATURITY_DATE/YYMD = IF COUNTRY EQ 'ENGLAND' THEN 20130625 ELSE
IF COUNTRY EQ 'FRANCE' THEN 20140725 ELSE
IF COUNTRY EQ 'ITALY' THEN 20150825 ELSE
IF COUNTRY EQ 'JAPAN' THEN 20160925 ELSE
IF COUNTRY EQ 'W GERMANY' THEN 20171025 ELSE 20201225;
END
TABLE FILE CAR
SUM
AVE.MATURITY_DATE AS AVG_MATURITY
ON TABLE HOLD AS TEMP2
END
DEFINE FILE TEMP2
TODAY /YYMD = 20121225;
CURR_MONTH /M = TODAY;
AVG_MONTH /M = AVG_MATURITY;
AVG_REMAINING_YEAR/I8 = DATEDIF(TODAY, AVG_MATURITY, 'Y');
END
TABLE FILE TEMP2
PRINT
TODAY
AVG_MATURITY
AVG_REMAINING_YEAR
COMPUTE AVG_REMAIN_MONTH/I2 = IF AVG_MONTH - CURR_MONTH GT 0 THEN AVG_MONTH - CURR_MONTH ELSE 12 + (AVG_MONTH - CURR_MONTH);
END

Notice the calculation for the remaining month.

I may be answering a question that wasn't asked, but this is how you would compute the average date if you wanted to answer the question -"What is the average order date by product?"

-* File ex_Average_Date_IBI.fex
DEFINE FILE GGORDER
OrderDateTime/HYYMDI=HDTTM(ORDER_DATE, 8, OrderDateTime);
END
TABLE FILE GGORDER
PRINT
     OrderDateTime
BY  LOWEST PRODUCT_ID
ON TABLE HOLD AS ORDERS
END
DEFINE FILE ORDERS
  FIRST_DATE/HYYMDI = IF PRODUCT_ID EQ LAST PRODUCT_ID THEN LAST FIRST_DATE ELSE OrderDateTime;
  DELTA_TIME/D20.3 = HDIFF(OrderDateTime,FIRST_DATE,'SECONDS','D20.5');
END
TABLE FILE ORDERS
SUM
  CNT.PRODUCT_ID AS 'N'
  DELTA_TIME
COMPUTE AVERAGE_ORDER_TIME/HYYMDI = HADD(FIRST_DATE,'SECONDS',(DELTA_TIME/CNT.DELTA_TIME),8,'HYYMDI');
BY PRODUCT_ID
END

It takes the first order date for each product and computes the difference in time between it and each of the following dates, sums the differences and divides by the total number of orders.