1 2 Previous Next 15 Replies Latest reply on Apr 1, 2019 9:07 AM by 3257177

# Previous Month MTD and Previous Year same Month MTD in rpd

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!

• ###### 1. Re: Previous Month MTD and Previous Year same Month MTD in rpd

Take a look at Gianni's blog about time series functions.

• ###### 2. Re: Previous Month MTD and Previous Year same Month MTD in rpd

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.

• ###### 3. Re: Previous Month MTD and Previous Year same Month MTD in rpd

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

• ###### 4. Re: Previous Month MTD and Previous Year same Month MTD in rpd

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 ...

• ###### 5. Re: Previous Month MTD and Previous Year same Month MTD in rpd

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

• ###### 6. Re: Previous Month MTD and Previous Year same Month MTD in rpd

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

• ###### 7. Re: Previous Month MTD and Previous Year same Month MTD in rpd

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

• ###### 8. Re: Previous Month MTD and Previous Year same Month MTD in rpd

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

• ###### 9. Re: Previous Month MTD and Previous Year same Month MTD in rpd

TODATE(Measure,month level from hierarchy)

• ###### 10. Re: Previous Month MTD and Previous Year same Month MTD in rpd

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.

• ###### 11. Re: Previous Month MTD and Previous Year same Month MTD in rpd

Thanks Christian. But I am sure formula is correct. Because rolling 3 months, rolling 1 month and month ago is working perfectly.I will keep you posted  on monday along with screenshots which will include column formula and hierarchy.

• ###### 12. Re: Previous Month MTD and Previous Year same Month MTD in rpd

3257177 wrote:

But I am sure formula is correct.

3257177 wrote:

...but for previous Month MTD and previous year same month MTD is not working as expected.

You are sure it is correct and you say it isn't working "as expected", but still you don't want to post the exact formulas you are using for the 3 cases...

• ###### 13. Re: Previous Month MTD and Previous Year same Month MTD in rpd

sigh. amen.

• ###### 14. Re: Previous Month MTD and Previous Year same Month MTD in rpd

I wasnt having access to my system as I have already left for the day yesterday. So I have clearly mentioned in my last comment that I will paste formula and screenshots on monday.

I have clearly mentioned that MTD formula is correct and is working as expected. Previous month Mtd and previous year same month mtd is not working. I have asked for suggestions on how can we achieve it.

Thanks!

1 2 Previous Next