Skip to Content
author's profile photo S Singh
0
S Singh

Display Rolling Year (36 month)in Webi Cross tab

Posted on Jul 15, 2020 at 06:12 AM 75 Views

Hi Gurus,

In crosstab is there any way to restrict display only 36 month data i.e from 06/2021 to 07/2018 in webi.Currently block is showing 48 month data.

Source is Bex.

Any suggestion will be appreciated.

display-only-062021-to-062018-36-month-only.png (94.2 kB)
Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    author's profile photo Saurabh Suresh Sonawane
    Saurabh Suresh Sonawane
    Posted on Jul 15, 2020 at 01:29 PM
    0

    Hi

    Sorry for lot of Question i ask to you

    please find the below solution

    step 1 = Create a new dimension yearmonth (this is to find max month in the report )

    yearmonth = =ToDate([year]+[month];"yyyyMM")

    Step 2 = create a new variable to find max of month in the report

    max month = =Max([yearmonth] Where (Not(IsNull([forecast])))) In Report

    step 3 = now we have to find the min date

    min date =RelativeDate([max month];-35;MonthPeriod)

    step 4 = now create a new variable forecast new =

    =[forecast] Where ([yearmonth] Between([min date];[max month]))

    Thanks,

    Saurabh S.


    capture.jpg (29.7 kB)
    capture2.jpg (56.7 kB)
    capture3.jpg (59.0 kB)
    capture3.jpg (59.0 kB)
    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Saurabh Suresh Sonawane
    Saurabh Suresh Sonawane
    Posted on Jul 15, 2020 at 08:29 AM
    0

    Hi

    step 1- Get a max month in the table (by using max function)

    step 2 use a relative date function and base on the max month in the report you will get a 36 month

    step 3 create a new variable monthnew apply a where clause on the month variable (apply a between filter base on the 2 variable you got from step 1 and step 2.)

    step 4 use the new month in the cross tab.

    Thanks,

    Saurabh S,

    Add a comment
    10|10000 characters needed characters exceeded

    Comment on This Answer

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.