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