Length of Service calculation in OBIEE with years, months and days

Martin van Donselaar · May 18, 2018 9:30AM

@Jerry Casey Thanks for your contribution, however, Please do not copy content from other sources here, but link to the content instead.

Jerry Casey · May 18, 2018 10:33AM

Thanks. Just edited my answer.

aPsikus · May 22, 2018 8:10AM

Thank you Jerry.

One additional question: to have it in one field it need to be concatenated. But CONCAT worsk only with CHAR. To have CHAR, it need to be changed to INT (as othwerwise CAST to CHAR do not work, becuase there is a lot of place after comma).

But when CAST Month calc to INT, it is changed/increased by 1. Any idea why?

Jerry Casey · May 22, 2018 9:01AM

I believe the issue is that the result of the MOD statement is not an integer, even though displayed as one. So when you cast to an integer, it is applied to 6.77..., not 6 in your first example. So, that "Months" statement has to be nested in a TRUNC function (bolded below). This statement:

EVALUATE('TO_CHAR(%1,%2)' as char,EVALUATE('TRUNC(%1)' as integer, (EVALUATE('MONTHS_BETWEEN(%1,%2)' as double,date '2018-05-09',date '1952-10-16') /12)),'99')||' years, '||EVALUATE('TO_CHAR(%1,%2)' as char,EVALUATE('TRUNC(%1)',EVALUATE('MOD(%1,%2)' as integer,(EVALUATE('MONTHS_BETWEEN(%1,%2)' as integer,date '2018-05-09',date '1952-10-16')),12)),'99')||' months, '||EVALUATE('TO_CHAR(%1,%2)' as char,31*(EVALUATE('MONTHS_BETWEEN(%1,%2)' as double,date '2018-05-09',date '1952-10-16')-EVALUATE('TRUNC(%1)' as double,EVALUATE('MONTHS_BETWEEN(%1,%2)' as integer,date '2018-05-09',date '1952-10-16'))),'99')||' days'

Produces this:

Also, I'm using "Evaluate(TO_CHAR)" because when I tried to use CAST(xxx as char) on the server functions, I couldn't concatenate them without getting an error message.

aPsikus · May 23, 2018 3:41AM

Great hint.

Thank you for help.

Oracle Analytics Forum

Categories

Length of Service calculation in OBIEE with years, months and days

Answers