Oracle Analytics and AI

Vishnu Prashant

Hello,

I have a question regarding the MTD (Month-To-Date in time-series) column behavior in OBIEE (v12.2.1.2.0). Below is the scenario:

Let's assume the current month is February 2019. Now if I select Calendar Month column and Current MTD and Previous MTD metric columns in the Criteria and the Current Month (i.e. Feb-2019) in the filters and check the results, the value returned by the Previous MTD metric takes into consideration, only the first 28 days of January i.e. from 1st Jan to 28th Jan only, i.e. similar to the number of days in Current MTD metric. But it is NOT taking 1st Jan to 31st Jan into consideration (and THIS is the business expectation from the Previous MTD metric).

Is this the expected and default behavior of OBIEE?

Is there a way I can configure the Previous MTD metric to take 1st Jan to 31st Jan into consideration in this case?

Regards,

Vishnu

Gianni Ceresa

You are using "to date" to calculate a month-to-date measure and set the point of view of the analysis at the month level?

Your month to date is acting just like a normal aggregation as at the month level your month-to-date is expected to be the whole month all the time.

You better use a more appropriate formula, one which will even be faster and simpler in the generated query, because you are not using your month-to-date at all in that situation.

In a way the month-to-date behave correctly: it's written to work at the day level and aggregate all the days of the month up to the current day, so when you select February you are implicitly selecting day 28 (or 29 if it was a different year), therefore the formula is working fine.

If you want the whole month don't use a month-to-date column.