Skip to Content
avatar image
Former Member

Previous Month & Last 12 Month Based on UserResponse

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • avatar image
    Former Member
    Nov 14, 2013 at 09:40 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member 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,

  • avatar image
    Former Member
    Mar 16, 2014 at 05:44 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Jul 27, 2014 at 02:51 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 07, 2015 at 02:35 AM

    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")

    Add comment
    10|10000 characters needed characters exceeded