Previous Month & Last 12 Month Based on UserResponse

[Former Member]

Dear All Guru,

I have a report in which i need to show previous month and last 12 month data based on a UserResponse from a month selection.

Report Scenario Example:

User was ask to select [Successful Date] from the prompt which using 'BETWEEN' (From and To). Based on the selection, e.g

1 Nov 2013 to 31 Nov 2013.

Based on the selection above, i should show the result for previous month and last 12 months. Example

1. Previous Month = October 2013 to Nov 2013 result  (1 October 2013 - 31 Nov 2013)

2. Last 12 month = Nov 2012 - Nov 2013 (1 Nov 2012 - 31 Nov 2013)

I am wondering:

1. How should i write the previous month and last 12 month variable based on UserResponse?

2. How do i filter it based on this variable

Regards,

Joe

[Former Member]

Just happened to see this post. Late reply but still hopes it helps someone.

My approach was to use a formula field in webi

=[CumlMonth] Where (Year([CumlMonth])<Year(CurrentDate()) And Month([CumlMonth])="September")

[Former Member]

Hi, I am working on SAP BO4.0.I have a requirement of creating a month filter on a date which will be between two ranges.The first range is the user response and the second range will be 6 months added to that user response.For eg:-If a user enters 1 as the response for month prompt then the date will be filtered between 1 and (1+6)=7.The database used is SAP HANA. Please let me know if further clarifications are required. Any help on this will be appreciated. Thanks in advance.

[Former Member]

Hi Guys,

I was struggling to the formula since last week and got the one.

I had a requirement to design a webi report which will show 13 months (in total) along with their values. Backend was BW/BEX and I got Calendar Year /Month object in yyyy.MM format with data of 6 years. I dragged Cal Year/Month object with Value on report panel. There were around 70 rows on a report block. To restrict it to show for only previous 12 months along with current Month, I followed below steps

Report requirement to show,

Mar 2013      | 200

Apr 2014       | 120

May 2014      | 120

.

.

.

.

Mar 2014      | 120

Follow the procedure to get this designed on your web intelligence report;

Create a Variable (Name it the way you want) and write a formula as is;

13 Months =((DaysBetween(ToDate([Cal. year / month] ;"yyyy.MM");ToDate((Max([Cal. year / month]) In Block);"yyyy.MM"))+1)/31) 

And now apply a block filter as;

Right Click on your report block; select ADD FILTER option from the menu. Make sure you are in a MODIFY mode.

Click on Add Filter on upper right corner of window (which will be opened)

Select the Object (Created Variable) and select LESS THAN OR EQUAL TO as operator and set a value of 12.

Click OK...

And your report with Last 12 Months along with current month ll be ready.

Warm regards,

Chaitanya

[Former Member]

Hi Yeat,

Please refer to below article from Dave which provides different type of logic to handle dynamic dates in Webi. These logic can be implemented in any version of Webi (3.x, 4.x).

http://www.dagira.com/category/design/dynamic-dates/

In case if you face any issue while implementing then let us know.

[Former Member]

Hi Hardik,

I read this before. But  i am still struggling. Currently , i am trying to write a variable based on user selection for a start date and a end date. Based on the input, i will then need to generate the result for previous month.

Example:

if user select 1 nov 2013 to 30 Nov 2013.

I shall return a previous month result which is from 1st October 2013 to 30 Nov 2013.

I have try to create a variable But i am struggling for a week already.

=DaysBetween(Format(ToDate(UserResponse("Enter Value for Completed Date (Start):");""); "MM-dd-yyyy hh:mm:ss a");

                      (Format(ToDate(UserResponse("Enter Value for Completed Date (End):");""); "MM-dd-yyyy hh:mm:ss a"))

I got some error for this.

The expression or sub-expression at position 13 in the 'Days Between' function uses an invalid data type

[Former Member]

Hi Joe,

You can utilize query-on-query/Results from Another Query option where :

1. Create an object called "Previous Month" in universe with date function of database
2. Also add "Prompt" in "Previous Month" function
3. Now when you create a query with "Previous Month" function then it will prompt and what ever user selects based on that, it will provide previous month date value
4. This "Previous Month" object can be used as filter to second query using "Result from Another Query" option

For more info on Results from Another query:

http://scn.sap.com/docs/DOC-21169

[Former Member]

I wish to do that, but I don't have the access in universe. I can only done it in the webi level. That is why it bother me.

[Former Member]

Hi Joe,

I am not sure how would you achieve this at report level because you want the query to fetch data based on user response. So this user response function has to be defined at  universe level in order to fetch required data.

Other option is that you fetch all the data from database in report, and based on user response and "RelativeDate()" function in webi, filter and show only required data.

[Former Member]

That is what i am trying to do, but i have no idea how to do it.  i Mean the second option.

[Former Member]

What is the exact value returned by following prompt?

=UserResponse("Enter Value for Completed Date (Start):")

=UserResponse("Enter Value for Completed Date (End):")

[Former Member]

I try this. if user select Nov 1 and Nov 30. the result will show as this below:

Completed Start Date Start) : 1 Nov 2013

Completed Start Date (End): 30 Nov 2013

[Former Member]

Try converting one user prompt at a time:

Create a variable with below formula and see if that fine.

=ToDate(UserResponse("Enter Value for Completed Date (Start):");"d MMM yyyy")

If above works out then try same for End date. And then you can apply DaysBetween functions on both variables.

[Former Member]

Ok. I have done till the part the date between.

DaysBetween(ToDate(UserResponse("Enter Value for Completed Date (Start):");"MM-dd-yyyy");

ToDate(UserResponse("Enter Value for Completed Date (End):")); "MM-dd-yyyy"))

I would like to know, what is the next step i should do? I wonder how am i going to filter from previous month and Last 12 month? Do i need to create another new variable?

[Former Member]

For this requirement here are the steps that will work..Although there is some negative performance implication.. you can do similar thing using input control as well..

1.Create an additional separate dummy query with with one date object only..

2. Create the prompt on that dummy query which will not affect your selection

3. Get a variable in report using userresponse() to capture the prompt value and get the last month of the data retrieved by the logic

4. Then create another variable to get the if date falls within the range and assign the records as 1 and if they are outside the range then 0

5.. Create a block level filter to show only records where the value is 1

If you face any challenges with this let me know, I have one more option to do the same thing which is more complex but will be better performing which I can explain..

[Former Member]

Hi Durga,

I am trying different way now. I just prompt the user to select the Successful Date (Stat) and Successful Date (End)

However, I have a report requirement in which I need to schedule a report for the 1st day of the next month.

Thus, the user would like to see the below report:

1. Previous Month

2. Last 12 Month

Please guide me through this.

Regards,

Joseph

[Former Member]

For the scheduling requirement I will suggest to do the magic date approach that Dave suggested here.. http://www.dagira.com/2008/07/21/using-a-magic-date-value-in-prompts/

This approach works pretty nicely for automatic scheduling requirement..

[Former Member]

I read the whole post, but i still confuse how the 'magic date' work in SQL server. Thus, how do i schedule previous month or whatever month i want? lolx

[Former Member]

Hi Durga,

My requirement is also the same as Yeat, to get the last 13 months  based on the user response.

But in the solution you have discussed above, you have referred some range. that is not clear to me.

Range Yeat has mentioned is something like this:

User was ask to select [Successful Date] from the prompt which using 'BETWEEN' (From and To).Based on the selection, e.g

1 Nov 2013 to 31 Nov 2013.

However in my case, the user has to select just a date only(not range using between) and based on that last 13 months should be displayed.

thanks

Vinod

[former_member539471]

Hi Hardik,

I have similar issue....

I need to display data based on user response. If user selects 201406 then I need to display data for 201403

prompt year_month: 201406(eg, it can be any year/month)

Current status (eg user input )           Previous status (based on user input - 3months i.e 201403)

            0                                                0

            1                                                 1

[Former Member]

Hi Hardik,

I have a requirement where i have to display only one year data in report at webi level.

For example:- One  Year is  May 2016 to June 2015 and once the month changes like june 2016 then  the data in the report should show  june 2016 to july 2015..

how  can  we achieve this at report level,  there are no prompts . i  have two Universe  level object.

which i can use in creation of the variable.

Regards,