6 Replies Latest reply on Feb 15, 2016 6:25 AM by Ganesh S

# Dynamic last 12 month sales numbers using year and month parameter

Guys,

Can anyone help me on the below task,

I need to viz latest sales and last 12 month sales based on the user selected year and month changes, so i need these two should be dynamic.

For example if user select 2016 in Year and Feb in month parameter, i need 2016 sales data in latest month and i need previous 12 month sales data in last 12 month. Jonathan Drummey

• ###### 2. Re: Dynamic last 12 month sales numbers using year and month parameter

Howdy Ganesh,

Please see the attached workbook. I've created a parameter that allows the user to select a month/year and then I created a calculated field that references this value. I've made it so that the calculated field is a filter and it returns any data that's within 12 months of the parameter date, but not greater than the date. Hence if you have data for 2010-2015 the end user would select a date in 2014 and would not be burdened with the extra data points after their chosen time.

Cheers!

Carl Slifer

InterWorks

• ###### 3. Re: Dynamic last 12 month sales numbers using year and month parameter

1) Create a Calculated Fields as below to get the Months

Name: Month

Syntax: month([Order Date])

2) Create a Calculated Fields as below to get the Years

Name: Year

Syntax: year([Order Date])

3) Create parameters for both Month, Year.

4) Then make a date as per selections of Parameters

Name: Selected Date

Synntax: date("01" +"/" str([Month Parameter] +"/" str([Year Parameter]))

5) Then create a calculated field as below,

Name: Sales - Selected Period

Synyax: if datediff('month', datetrunc('month',[Order Date], [Selected Date])>=0 and datediff('month', datetrunc('month',[Order Date], [Selected Date])<=12 then [Sales] end

6) Now drag the [Order Date],  [Sales - Selected Period] fields.

7)  Drag the  [Sales - Selected Period] onto filters shelf then select "Sum" then select "Special" tab then select "Non-null values" option.

8) Now you will ge the Data for last 12 months of selected Year, Month values from Parameter.

Best Regards

Kumar

• ###### 4. Re: Dynamic last 12 month sales numbers using year and month parameter

Thanks Kumar,

Could you explain what this field does, date("01" +"/" str([Month Parameter] +"/" str([Year Parameter]))

also this is not getting validated

• ###### 5. Re: Dynamic last 12 month sales numbers using year and month parameter

We are creating a date from Year, Month Values.

EX: Year = 2012, Month =3 then it will give 1st Mar 2012

I have tested that calculated field before share the solution, and it works well.

• ###### 6. Re: Dynamic last 12 month sales numbers using year and month parameter

No Luck,

both my month and year parameters i created already as string data type, is there anyway of doing this?