Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 211 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 77 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
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!
Answers
-
Take a look at @Gianni Ceresa blog about time series functions.
0 -
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.
0 -
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
0 -
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 ...
0 -
we would be having issues with the leap year. if we exclude that scenario how can we achieve it.
0 -
If you exclude leap years, then normal time series functions will work.
0 -
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
0 -
The question is - what's your *exact* todate formula?
0 -
TODATE(Measure,month level from hierarchy)
0 -
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.
0
