Previous Month MTD and Previous Year same Month MTD in rpd

3257177

Hi All,

We are trying to create MTD, Previous Month MTD and Previous Year same month MTD for adhoc reporting in OBIEE12c.

we can somehow achieve this in analytics.but for us we need them to create in rpd for adhoc reporting.

we have built time series hierarchy and rolling months rolling weeks everything works out even MTD worked well for us as we have used todate for MTD.

Can someone please let me know on how can we create previous month MTD and previous year same month MTD in rpd.

consider Current_date as 29-Mar-2019

MTD                                            PMTD                                          PYMTD

1Mar 2019-29Mar 2019           1Feb2019-28 Feb2019             1Mar 2018-29 March 2018

above scenario should work when we add date column in the report.

my sample report have below columns

Month     Date  BaseMeasure  MTD  PMTD     PYMTD

Thanks!

Joel

Take a look at @Gianni Ceresa blog about time series functions.

Gianni Ceresa

As a side question, let's say current date = 28 Feb 2017

What is your PMTD and PYMTD ?

(keep in mind 2016 was a leap year, 29.02.2016 was a thing)

Because the answer to your questions first depend on the business rules behind these things.

3257177

Thanks Gianni for your question.

we had same question in our mind still we need to go back to customer on that part.

we are thinking

if our current_date is 28 Feb 2017 PMTD would be 1Jan2017 to 28 Jan2017

and PYMTD would be 01FEB2016 to 28FEB2016

Gianni Ceresa

So 29 Feb 2016 will never exist in your results as well as 29-31 Jan 2017.

Define the full logic with the business first, covering all these weird cases first. Clearly challenging them on their decision, the technical solution for that logic could change based on the answers ...

3257177

we would be having issues with the leap year. if we exclude that scenario how can we achieve it.

[Deleted User]

If you exclude leap years, then normal time series functions will work.

3257177

Hello christian,

For MTD i have used todate and it is working fine. but for previous Month MTD and previous year same month MTD is not working as expected.

Date                  Measure     MTD    PMTD   PYMTD

1-Jan-2019          10              10

2-Jan-2019            20            30

.

.

1-Feb-2019          40              40        10       value which was there on 1-Feb-2018

2-Feb-2019           50             90         30      value which was there on 2-Feb-2018

[Deleted User]

The question is - what's your *exact* todate formula?

3257177

TODATE(Measure,month level from hierarchy)

[Deleted User]

Your EXACT formula.

Because if you put the wrong value for the level and the level has wrong keys then it won't work. An MTD measure knows when its month starts. It can't be wrong. only your formula or dimension can be wrong.