Average Date Calculation

damen

Hi,

Is there a way to do an average date calculation in Domo? I am working with a table that has close to 30k rows and all we are trying to do is get an average date for the date column.

Is this possible? I looked in the beast mode functions and could not confirm it was in there.

Thanks

nmizzell

@damen I think that the average date will give you an incomplete or inaccurate result for this application. I suggest you compute each security's Macauly and Modified duration https://www.investopedia.com/terms/d/duration.asp . This is the traditional metric for determining the "Average date" / interest rate sensitivity of a bond portfolio.

MichelleH

@damen Does AVG(`Date`) work?

damen

@MichelleH it gave an integer that was not correct. I believe excel can do this but am not sure how Domo does.

MarkSnodgrass

One possibility is to convert your date to an integer using the UNIX_TIMESTAMP function and then use the AVG function and then convert that value back to a date using the FROM_UNIXTIME function. It would look like this:

FROM_UNIXTIME(AVG(UNIX_TIMESTAMP(datefield)))

You might need to add a round function in there as well in case the average returns a decimal number, which the FROM_UNIXTIME function would have a problem with.

MichelleH

@damen I agree with @MarkSnodgrass' suggestion. Out of curiosity, what are you trying to represent by taking an average of a date?

damen

@MichelleH Investors want to know the average dates of loans that are in bonds and indentures. It gives them an idea of potential turnover time for the loans in the categories we are describing.

nmizzell

@damen I think that the average date will give you an incomplete or inaccurate result for this application. I suggest you compute each security's Macauly and Modified duration https://www.investopedia.com/terms/d/duration.asp . This is the traditional metric for determining the "Average date" / interest rate sensitivity of a bond portfolio.