6 Replies Latest reply on Sep 22, 2016 4:24 PM by Shesil Parmar

# Last 3,6 and 12 months using parameter

sorry guys if this has been asked before, i have had look but couldn't find exactly what i was after.

so i need to navigate data using parameter; so i have managed to work out somewhat but i believe there should be some proper way to do this.

so in my workbook, when someone select last 3 months and then it should show current month - last 3 months (ie someone using report in Sep then last 3 months will be June, July and August , same with 6 and 12 months) so basically it should not include the current month.

but my calculation count month from current month, couldn't work it out.

and need another paramater or option when selected last 3 months (ie someone using report in Sep then last 3 months will be July, Aug and Sept, same with 6 and 12 months) so little different than above,

• ###### 1. Re: Last 3,6 and 12 months using parameter

Hi Shesil,

See attached.

I made your parameter an integer, and then created the following formula for the "Date Range" field:

if datediff('month',[Month],today())>=1

and datediff('month',[Month],today())<([Parameters].[Month])+1

then [Month] else null end

In short, this equation says: if not this month, and within the last 6 months (7 including this month), show me the date.

Hope this helps!

1 of 1 people found this helpful
• ###### 2. Re: Last 3,6 and 12 months using parameter

thank you  Kathryn.. this is perfect !

• ###### 3. Re: Last 3,6 and 12 months using parameter

is there any way to work with String data type parameter, unfortunately i cannot change in my workbook as its been used in many reports,

and how can i achieve second condition

and need another paramater or option when selected last 3 months (ie someone using report in Sep then last 3 months will be July, Aug and Sept, same with 6 and 12 months) so little different than above,)

?

• ###### 4. Re: Last 3,6 and 12 months using parameter

Hi Shesil,

Please see to the attached workbook. Hope it helps.

My suggestion is to use the default Relative Date filter for the Last 3 months or so.

See to the 1st Sheet.

Second sheet uses Parameter

• ###### 5. Re: Last 3,6 and 12 months using parameter

Hi Shesil,

If you keep your Parameter as string, just name the aliases what you are currently using and keep the fields as '3', '6', and '12'.

Then, in the original equation I gave, see if adding int() before calling the parameter will do the trick:

if datediff('month',[Month],today())>=1

and datediff('month',[Month],today())<int(([Parameters].[Month]))+1

then [Month] else null end

1 of 1 people found this helpful
• ###### 6. Re: Last 3,6 and 12 months using parameter

thanks kathryn, it solved my problem