Previous month data using current month input selection

[Former Member]

Hi,

I am looks for suggestion in fulfilling a report requirement. Our users want to see selected month and previous month data in 1 report table

Data is at a daily level and users doesn't want to give 4 date selections (2 for getting current month data query and 2 for previous month data query) to get the desired output

Is there a way to get current and previous month data by just current month start date and end date

Our Environment: BO 4.0 SP06, WebI report, Oracle 11g

Thanks,

Mitch

[Former Member · Accepted solution]

Hi,

First you create two dimension objects in the universe, where you calculate current month, and previous month. Then you create a month dimension which takes the month from your date dimension.

Then you create your first query and set your newly created month dimension to be equal to your current month, as a filter.

Lastly you create the previous month query and filter in the same way, but with the previous month object.

In your report, you then merge the common dimensions you want in your table, and then you are able to get the two measures next to each other.

Hope this helps.

/Kris

[Former Member · Accepted solution]

Hi Kris,

Thanks for your reply

Looks like, I have miss communicated the scenario. The report doesn't have to be run always for current month and previous month. It is up to users to select the start date and end date

Ex: Users wants to select 1 month range (Jan 1st - Jan 31st ) at webi prompts, and, they are expecting previous month data (Dec 1st - Dec 31st) also with out giving previous month dates. Is this possible?

Sorry for the miss communications

Thanks,

Mitch

[Former Member · Accepted solution]

No worries, it is possible yes.

You need to create a new dimension called "relative previous month", that calculates previous month (Previous period) based on the object (Period) you use for the selection (You can do this in the universe).

Then create a new query in your report where you only add a month (Period) object in the filter as a prompt and the same object along with the new one into the result objects.

Then in your "Current Month" queries you set the object you want to filter with, to get the result of another query and the select the (Period) object. And in the "Previous Month" queries you do the same but with the "Relative previous Month" object.

Now if you run this query, you just select the "Current Month" in the prompt, and the rest of the queries will get filtered accordingly.

This might be a bit abstract, but let me know if you need me to go in depth with some of it.

/Kris

[Former Member · Accepted solution]

Thank you very much for your reply and really appreciate your help

Please see below if my understanding is correct

Note: Data at the backend is at daily level so, we don't have month object. Filtering is done using date parameter

1) Create "Relative Previous Month" object in universe by using the Date object that I use for filtering the data

Ex: If I use Selection date as a prompt (for filtering data of current month) then I have to create "Relative Previous Month" using

TRUNC(TRUNC(SELECTIONDATE,'MM')-1),'MM') - Previous Month start date

TRUNC(SELECTIONDATE,'MM')-1  - Previous Month end date

to get start of previous month

2) Create a query with these 2 new objects in result objects pane, with query filter on Selection Date

3) In the previous month query (same as current month query), use "Result from another query" option in prompt of "selection date" object 

Thanks,

Mitch

[Former Member · Accepted solution]

Hi Mitch,

That sounds right yes. I don't know about your formulas, as I am not that experienced with Oracle SQL.

Anyway, you should be able to create a dimension object based on the date, that extracts the "year.month" from the date. That will eliminate the need for a period selection, as you can do with a one year.month selection instead.

/Kris

[Former Member · Accepted solution]

I have created the objects and tested them and they are working as expected

Really appreciate your help Kris!

[Former Member · Accepted solution]

Hi Mitch,

I have same scenario, could you please explain in detail how you did it?

Thanks,

Venkat