To display data of Last 12 months in Analysis

Rai Qaiser Hussain

Hi All

I have a requirement to show data of last 12 months, I read following links but could not fullfil my requirement properly.

OBIEE in IL: OBIEE - Few options to compare data to previous month, Year or something...

https://www.clearpeaks.com/retrieving-revenue-average-of-last-n-months/

https://shivabizint.wordpress.com/2008/09/19/rolling-months-data-for-year-month-prompt-in-obiee/

Final Work I did

1. function used to get the last 12 month MIN(TIMESTAMPADD(SQL_TSI_MONTH,-12,   "Revenue"."D01 Time"."Calendar Date" ))
2. Created prompt and added into presentation variable-- result is ok at prompt level
3. Passed presentation variable into filter (@{presentation variable}) , analysis does not return any data, as per query under advance tab, presentation variable does not pass value

anyone please guide what is wrong in this solution or any better solution please

Fig.1, this is actual requirement but it is achieved after hard coding the month with between function

Jerry Casey

This one gets you the last 12 months, based on the first day of current month.

"Your Datefield" BETWEEN TIMESTAMPADD(SQL_TSI_MONTH,-12,TIMESTAMPADD(SQL_TSI_DAY,-(DAYOFMONTH(CURRENT_DATE)-1),CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY,-(DAYOFMONTH(CURRENT_DATE)),CURRENT_DATE)

You can replace the bolded text with CURRENT_DATE, or whatever you want to count back from.

Thomas Dodds

A properly formed calendar/date dimension is what I've counseled in the past (to you and others) ... do you really want to have to go through this EVERY time you want another time series measure?  Build it right ONCE - use it properly MANY TIMES.

[Deleted User]

Sorry @Jerry Casey  but I have to support Thomas' input on this and point out that your solution - while technically correct has to be regarded as dangerous.

Heeding the last posts and questions this can lead the OP to blindly copy + paste code and calculate things manually rather than model correctly. I've seen it way too many times. Provide some code that works and any notion of critical thought or reflection goes out the window.

Jerry Casey

HI Christian and Thomas,
I completely understand what your saying about proper modeling of the data.  Many of us aren't involved in model building and are trying to meet expectations with what we have to work with.  If one of the hacky workarounds that I've used in the past answers someone's question, should I refrain from offering it?  Please be assured I take no offense at being told when it's not the best way.
I greatly appreciate all that I've learned from the real "gurus" on this site, and just want to contribute when I can.
Jerry

Rai Qaiser Hussain

Hi @Thomas Doods

As I understand your comments, you are talking about following (Fig. 1). If not, please explain your reply in little more detail for my better understanding.

Fig.1, Time Dimension

Rai Qaiser Hussain

Hi @Jerry Casey

Thanks, Hopefully this is what I required, I shall update once implemented/tested

[Deleted User]

Thanks for being understanding Jerry. It's just something which is extremely tiring and from time to.time wamts to break free and said out loud

Rai Qaiser Hussain

Hi @Christian Berg

Would you please explain your solution in little more detail, definitely your proposed solution will the best as you are one of the most senior at this forum. and as a junior we need guidance of our seniors.

Jerry Casey

I do understand, as far as I can without having answered the 10,000 questions that you have.

Just to clarify when I might use the method I offered:

OBIEE is very effective at doing analytics on project schedules on a mass scale (much better than our scheduling software).  I use data models that contain 90+ date fields, including baseline, forecast and actual dates for many project milestones.

We do have proper dimensions on the key delivery milestones, but occasionally, I need to filter on one of the "non-dimensioned" dates.  I know it's not ideal, but it seems (to me) a bit unwieldy to have dimensions on all of them,

Thomas Dodds

a properly designed calendar/date dimension will allow you to do canonical time based calculations.   User picks a date or a range and the ago representations are all automatically done:  WAGO, MAGO, PAGO, QAGO, YAGO ... and the variances and % changes.  Think about setting yourself up for future success now.   Doing these things in the front end is all well and good in a pinch, but this should never be your long term strategy.

What I'm getting at is data modeling techniques that are applicable tool-agnostic.  Most of these tools are based on similar foundations of information modeling.