How to find out Tenure in Months of a customer? OBIEE

User_QVJMI

Hi all,

Please help me with this attribute, I am unable to achieve correct answer.

Tenure in Months

1- TIMESTAMPDIFF(SQL_TSI_MONTH,cast("Customer"."Customer Created Date" as date),cast(CURRENT_DATE as date))

Not giving correct answer.

Example:

Customer created date: 11-March-2021

Current date: 9-June-2021

Answer given by above sql: 3

The answer should be: 2

I have also tried other sql but unable to achieve correct answer, I cannot use Direct SQL method.

please help in this regard,

TIA

Gianni Ceresa

Hi,

The result you see is correct for the dates you provided: 3.

You are asking a different question to OBIEE than what the logic of your formula does.

TIMESTAMPDIFF at the MONTH level is translated, in an Oracle database, into ROUND(MONTHS_BETWEEN(...)).

Run this piece of SQL on your Oracle database and you see the behavior.

WITH t AS (
    SELECT
        TO_DATE('2021-06-09', 'YYYY-MM-DD')      AS current_date,
        TO_DATE('2021-03-11', 'YYYY-MM-DD')      AS customer_created_date
    FROM
        dual
)
SELECT
    customer_created_date,
    current_date,
    months_between(
        current_date,
        customer_created_date
    ),
    round(
        months_between(
            current_date,
            customer_created_date
        )
    )
FROM t

   

CUSTOMER_CREATED_DATE    CURRENT_DATE    MONTHS_BETWEEN(CURRENT_DATE,CUSTOMER_CREATED_DATE)  ROUND(MONTHS_BETWEEN(CURRENT_DATE,CUSTOMER_CREATED_DATE))
11-MAR-2021 00.00.00    09-JUN-2021 14.51.33    2.95545586917562724014336917562724014337    3

Now that you know how TIMESTAMPDIFF at the month level works, define the correct logic for your need.

PS: as you didn't define the business logic you are looking for, I can't give you any extra hint on how to get there. Define your business logic first, find the correct logical SQL formula for it, check the generated physical SQL logic, test it with borderline cases to make sure it keeps working as expected.

User_QVJMI

Hi Gianni,

My business case is to find out customer tenure from customer.customer created date dimension.

Reply to your answer: I totally understand your point that I am using Timestampdiff and it will provide month level answer.

But in my case what will be the answer then? As Month_between is Not available on OBIEE.

What I need to achieve

Customer created Date: 13-March-2021

Answer in month: 2

Please help!

Gianni Ceresa

As the example above shows, with customer create date = 11-March-2021 and current date = 09-June-2021, the result of MONTHS_BETWEEN would be 2.9554...

So what's the logic for you to say that 2.9554 should be 2?

Another example, MONTHS_BETWEEN 11-March and 12-June = 3.0323, do you want 3?

Are you saying your logic should be FLOOR(MONTHS_BETWEEN(....)) ?

You should first define what the business logic is, because what you call "customer tenure from customer created date dimension" has a meaning for you which isn't the same meaning all around the planet in every other company.

User_QVJMI

Totally agree with you Gianni.

I will further explain my business logic

Case: Tenure in Months of customer should be the no of months a customer has spend on your system. I will provide example also that I want to achieve on OBIEE, also Months_ between doesnt work on OBIEE so please help for alternative to achieve.

Example:

Customer Tenure= Customer Created date - Current date

1) Customer Tenure= 11-March-2021 - 10-June-2021

Answer will be= 2.9

What I want is= 2

2) Customer Tenure= 1-March-2021 - 10-June-2021

Answer will be= 3.2

What I want is= 3

I hope this helps. please help in providing a function or a logic to achieve this.

Thanks

Gianni Ceresa

Technically "MONTHS_BETWEEN" works, but this exact function isn't exposed in OBIEE. If you don't want to use evaluate to push down to the database FLOOR(MONTHS_BETWEEN(...)), you have to adapt the logic by knowing how OBIEE translate TIMESTAMPDIFF.

As said earlier, TIMESTAMPDIFF is MONTHS_BETWEEN, but it has a ROUND() applied to it which is annoying for your case.

A possible workaround would be to remove the impact of days of months in the calculation. In that way MONTHS_BETWEEN will already return integers directly without the impact of ROUND() (because rounding an integer is the integer itself), which makes TIMESTAMPDIFF safe to manage (for your need).

So if you would use the first day of the month of the customer creation date, and the first day of the current date month, you would have a very predictable result.

There is now only a last element you have to take care of: the day of the month of the creation date vs the day of the month of the current date. If the day of the month of the creation date is greater than the day of the month of the current day, you have to substract 1 from the TIMESTAMPDIFF calculation because the month isn't full now.

There you go: this gives you what you are after. OBIEE has all the functions for that: TIMESTAMPADD, DAYOFMONTH, TIMESTAMPDIFF, CASE...WHEN . You have everything you need for your job.

There are for sure other logical constructs which could give you what you need, but you can explore that on your side.

PS: I'm not writing the full OBIEE formula on purpose as I would need to ask for a salary at that point, but the whole concept is written and in minutes you have it in OBIEE LSQL.

User_QVJMI

Thanks alot Gianni for your kind help, you are the best.