I was asked to help with comparing data to previous period, month or year. There was another problem: No time dimension. I will describe here few options to do similar things.
(This post took me long time to finish. It started with 11.1.1.7 and was finished with 11.1.1.9 but should work with both)
Option 1: the correct option, use AGO function
This is the short and default answer. Create a date dimension. Make sure you have chronological key for at least date and month levels (date only might surprise you when comparing shorter and longer month) and use AGO function as described in the post: OBIEE and Time Series Calculations in analysis (Ago, Period Rolling, To Date).
For example: AGO("Base Facts"."Revenue", 1)
For example: AGO("Base Facts"."Revenue", 1)
The following options will be without time dimension. All done on analysis based on Sample Sales, with Product_Type, Revenue and some period (Year or Month or both).
Option 2: Calculating previous month using MSUM (relevant for non chronological as well)
The idea behind this option is the calculation:
Sum(Last 2 Periods) = Previous Period + Current Period. As a result:
Previous Period = Sum(Last 2 periods) - Current Period
How do I calculate Sum(Last 2 Periods)? Using moving Sum. In our case: MSUM(measure,2)
Example 1: I have only Month and Revenue.
To calculate previous Month I add a column with formula MSUM(Revenue,2)-Revenue
Next I can do any calculation I want with the 2 columns.
Example 2: having Product Type, Month and Revenue
When adding dimensions, things get a little complicated. I'll add "Product Type" column:
We have a problem with the first period. Instead of being zero or Null, it's the last value of the previous Product Type!
Since I'm lazy, I'll just add a case statement, for the first period of each product type, the value is NULL, else the previous calculation (just remember, first periods might be different for various Product Types).
First Period calculation: Min(Month by Product)
The case should be: CASE WHEN (Month=Min(Month by Product) then NULL ELSE (MSUS(Revenue,2)-Revenue) END
Next I can do any calculation I want with the 2 columns.
By the way, some basic math using msum(Revenue,2):
Prev. period: msum(Revenue,2) - Revenue
diff between 2 periods:2*Revenue - msum(Revenue,2) (the calculation: Revenue - prev. period)
% of change between periods: 100*(2- msum(Revenue,2)/Revenue) (the calculation: 100*diff/Revenue)
By the way, some basic math using msum(Revenue,2):
Prev. period: msum(Revenue,2) - Revenue
diff between 2 periods:
% of change between periods: 100*(2- msum(Revenue,2)/Revenue) (the calculation: 100*diff/Revenue)
Option 3: Calculating same month of Previous Year
In many cases the data has strong seasonality, so it's logical not to compare and make calculations for the 2 month, but rather the same month of previous year.
To make things easier I used constant Years calculation. You can create a general analysis by replacing it with Presentation Variables or based on Current Year calculation:
Current Year: YEAR(CURRENT_DATE)
Previous Year: YEAR(CURRENT_DATE) - 1
For this option to work I need the Month value to be the same for every year. In Sample Sales the month format is YYYY/MM, so I used the function RIGHT(Month, 2). Please note: in Sample Sales, both Year and Month columns are characters.
I filtered the data to be only Years 2009 and 2010.
Since I don't have a Year column. The initial data is sum of both years.
I'll add a column to calculate year 2010 Revenue only: FILTER(Revenue USING (Year = '2010')) and one for Year 2009 FILTER(Revenue USING (Year = '2009'))
In case you have a presentation variable, named, for example, P_YEAR1 with the year 2010 selected, you should have the following formula: FILTER(Revenue USING (Year = '@{P_YEAR1}{2010}'))
Now we can remove the Revenue column and do any calculation we want with the 2 columns. (In the example bellow, I'm showing only fist 4 month of year and calculate the difference between the two years):
If you want a nicer column headers, but don't want them to be static... In case you use the analysis in a dashboard, you can use presentation variables as column names, such as: @{P_Year1}{2010} and use the same Variables as the analysis filter.
Option 4: Creating specific calculations using the $number calculated item (relevant for non chronological as well).
Assuming we want to compare specific values, lets say we want to compare 2 first month, 2 last month and last vs. first...
We can create a new calculated item and use $number to point to specific row / columns:
A useful and less known fact $-number would count from the end.
For example if we have 12 month in a pivot:
We can add calculated item that returns the difference between first 2 month: $2-$1
the last 2 month (if I know they are 12 month)
Or if I don't know how many month they are ($-1 - $-2):
last month minus first month would be: $12-$1 (or $-1 - $1)...
We can create a new calculated item and use $number to point to specific row / columns:
For example if we have 12 month in a pivot:
We can add calculated item that returns the difference between first 2 month: $2-$1
Or if I don't know how many month they are ($-1 - $-2):
last month minus first month would be: $12-$1 (or $-1 - $1)...
Option 5: Using Filter to select specific periods (relevant for non chronological as well). Similar to option 3.
We can use filter function to return specific values.
For example we only want 2 first month of 2009 and the total revenue of 2009. I'll select the filter function:
Filter it by month:
select the condition:
and create the function:
the function is:
FILTER("Base Facts"."Revenue" USING ("Time"."Per Name Month" = '2009 / 02'))
We can replace the specific value (2009/02) with prompt.
Any calculation is now possible between the functions.
This will work if the aggregation level of the data is higher than the filter level. In our case a year:
Not if you add the month to analysis:
For example we only want 2 first month of 2009 and the total revenue of 2009. I'll select the filter function:
Filter it by month:
select the condition:
and create the function:
FILTER("Base Facts"."Revenue" USING ("Time"."Per Name Month" = '2009 / 02'))
We can replace the specific value (2009/02) with prompt.
Any calculation is now possible between the functions.
This will work if the aggregation level of the data is higher than the filter level. In our case a year:
Option 6: Using Evaluate with the (Oracle) DB LAG function.
We can use database functions such as Lag and run them from Evaluate function in OBIEE. I leave it to you, if you are interested.
