Restrict the data for 6 months based on user selection

[former_member334960]

Hi All,

I have a requirement to restrict my report to last 6 months data based on the user selected month in the selection screen.

Eg : I have Cal year/month field in the selection screen, if a user selects June 2020, he should be seeing months Jan to Jun in the below format.

If a user selects Jan 2020, he should be seeing Jan 2020 to June 2020.

If a user selects Feb 2020, he should see Jan2020 to Jun 2020.

If a user selects Mar 2020, he should see Jan2020 to Jun 2020.

If a user selects Apr 2020, he should see Jan2020 to Jun 2020.

If a user selects May 2020, he should see Jan2020 to Jun 2020.

If a user selects Jun 2020, he should see Jan2020 to Jun 2020.

If a user selects Jul 2020, he should see Feb 2020 to Jul 2020.

If a user selects Aug 2020, he should see Mar 2020 to aug 2020.

If a user selects Sep 2020, he should see Apr 2020 to Sep 2020.

If a user selects Oct 2020, he should see May 2020 to Oct 2020.

If a user selects Nov 2020, he should see Jun 2020 to Nov 2020.

If a user selects Dec 2020, he should see Jul 2020 to Dec 2020.

Month should always start from Jan and should not go into the previous year.

so for every selection a user makes, we need to show only 6 months data in the format shown above. This should be dynamic based on the year and month a user selects.

This is how my data looks like in the backend.

can someone please let me know how we can achieve this?

Thank you

Poojitha

[ayman_salem]

Here is an idea that you can adapt to suit your needs:

-- Define the following variables:

currentMonth. =MonthNumberOfYear(CurrentDate())

currentYear: =Year(CurrentDate())

Date: =ToDate([Cal Year Month]; "MMM yyyy")

MonthNumber: =MonthNumberOfYear([Date])

Year: =Year([Date])

minMonth: =If (Max([MonthNumber]) -5 ) In ([Year]) < 0 Then 1 Else (Max([MonthNumber]) - 5) In ([Year])

status: =If ([MonthNumber] >= [minMonth] ) Then 1 Else 0

-- Create your table and apply the following Filter and Input Controls to it:

Filter:

status Equal to 1

Input Control:

..

..

Hope this gives you the first step in solving your needs

[ayman_salem]

Do you need this at Unv or Report level?

Is the user selection a prompt or an input control selection?

Do you have also the month as a number in your backend data?

[former_member334960]

Hi Ayman,

I need to calculate this in webi report. and it is a prompt. Source used in Bex query.

[former_member334960]

Hi ayman.salem

I have split the Cal Year/Month field to derive month number and year in two different fields in webi

[ayman_salem]

Your requirement is not easy to meet because: First, the format for your "Cal Year / Month" backend data is not appropriate for queries like "less than or greater than".

Second, part of the solution should be performed at the unv/unx level to avoid unnecessary data in the report (reducing the amount of data stored in the report)

(note the difference between query data and data displayed
also the difference between query prompt and input control).