Time series measures -- ago, to_date function for trending comparison

Hello again

One of my audience brought up an interesting question a few days back. Below is the message:


Hi,
Please give me a solution for the below scenario.
i have pulled concept name, date, sale value(measures). my requirement is that the report should show sale value based onconcept name and current_year date and last year date of CY date' date like(12-sep-2012 & 12-sep-2011). It is like
concept name, CY_date, LY_date, Vales_Val_CY_date,Sal_val_LY_date. can i proceed with presenation variable or any other way. please give me solution ASAP.

Thank you.

For what I am understanding, which could be wrong by the way, the requirement is to compare the measurements taken over the time period over different years to see the trending statistics. This is pretty common in retail sales (I used to work as a part-time at shoe store at one point) that the business want to see the sales amount for certain products in March 2012 and then they want to see the sales amount for the same product in March 2011, 2010 etc to see a trend.  So ideally, the 2 fields should be side by side on the same report. It can be even trickier that when we are still in the middle of March, the total sales amount should be a month-to-date total because March isn't over yet. Then at the same time, they want to look at the equivalent month-to-date total last year. Since March 2011 is over, and what's stored in the database is likely the total sales of entire March 2011 since they do have the conclusion there. This comparison would be wrong. So we need to figure out a way go back in time and get the prorated equivalent period of the current year and get the result.

Now in OBIEE, we all know that we have a way to implement time series report by utilizing ago and to_date functions. In this case, we will continue to do so with a few expansions. So I am going to skip the details on how to set up the rpd for time series calculation by assuming the Date hierarchy is created with levels and keys properly defined. So let's get to the point.

Taking the above-mentioned scenario as an example, so I want to create a measure that gives me the current month-to-date sales total. Always, we have to consider the scenario when users want to run the report at any point in time during the month.  So in the below image, beneath the retail sales amount is MTD Retail Sales Amount, which is a pretty standard.




Now the tricky part is the second measure that I am about to create. This measure should give me the same time period equivalent of last year. A simple Ago function will work ONLY if we are looking at previous year's month totals. It WON'T work if user is querying during a given day before current month end and want to see the total amount over the same number of dates last year. Therefore, we have to do it differently here.

First of all, we do need to use the ago function, but it won't be on 'retail sales amount' column for the reasons stated above. Therefore, it has to be taking the result of 'MTD Retail Sales Amount' that I created earlier and get the equivalent 12 months ago. So it will be Ago(MTD Retail Sales Amount, Month, 12).

However, this is not going to be enough because the to-date function is going to add all of the days from the past up until now. So if we are talking March last year, then we don't want to add April, May, June of last year into the equation. Because changes are, the date dimension stores dates way ahead into the future, if we are in March 2012, that means April 2012 has no sales yet and therefore the users may not want to see the equivalent of April in 2011. Remember, April, May, June of 2011 will have to be based on the summary of sales made over the number of days equivalent to how many days are into April 2012, not the entire month.

So, this means a filter will have to be apply into the expression. We have to make this formula return data that are only up to current month. Hence, a dynamic variable will have to be created to capture the current month. Ideally, it show return data in the format of yyyymm and it should be numeric data type.

The statement is like so: Select To_number(to_char(sysdate,'yyyymm')) from dual

Now we have that, let's incorporate this filter logic into the expression of 'Last Year MTD retail sales amount'. This gives me the final expression:

FILTER( AGO("inSight Smart Mobile Backhaul"."LSP Facts"."MTD Retail Sales Amount", "inSight Smart Mobile Backhaul"."Date Dimension"."Month", 12) USING "inSight Smart Mobile Backhaul"."Date"."Year Month" <=  VALUEOF("Current_Month"))

A side note: I don't like to do ''<=" comparison on varchar datatype.

In some projects, month column can be stored as a varchar with the format like '2012 - 03'. If that's the case, just convert it to number by removing '-', or create a new column for it and use that in this formula. To convert into numeric datatype, just use replace function: cast(replace(column name,'-','') as integer)

Anyways, that's beside the point.

So after putting everything together, let's see what kind of report it looks like:

Woops, I have to confess that I messed up a little bit. Not because the implementation is not working, but because I purged the data before writing up this blog post. Therefore as you can see from the report, all we have in my testing database is the data of 2012 Sept. Therefore, same time last year sales amount is blank. I should've been careful next time. However, I can assure you that the data came out right from the same report before I purged everything.

Not convinced?

Go ahead and get it a try, experiment it!

Until next time

Thanks