4 Replies Latest reply on Mar 9, 2017 8:43 PM by Dale Forman

# Rolling 12 Months calculation

I need to count distinct values for rolling 12 months. But i need to show values on quarterly basis

So if i say Mar 2017, Then it should take values from 1 Apr 2016  to 31 Mar 2017

Dec 2016,                                                          from 1 Jan 2016  to 31 Dec 2016

Sep 2016,                                                          from  1 Oct 2015 to 30 Sep 2016

Using attached sample excel sheet  final values should be

Mar 2017 # 4  (Distinct count of A,B,C,D)

Dec 2016 #  5 (Distinct count of A,B,C,D,E)

Sep 2016 #  3 (Distinct count of A,C,E)

I have read many similar questions in forums and blogs.  I am sorry  ,if i am repeating already answered question.

Thanks

• ###### 1. Re: Rolling 12 Months calculation

You're not going to get a number for March 2017 because you have no data in your data source for that month.

But here is a way to do that.  It might get expensive for large data sets, but this method gets the job done.

Attached is a 9.3 example.

• ###### 2. Re: Rolling 12 Months calculation

I should show only points for Each quarter, Not month wise.

So there will be data point for Mar 2017 with value 4, i.e. 2017 Q1

for Dec 2016 ............ 5, i.e. 2016 Q4

I hope , it makes sense .

• ###### 3. Re: Rolling 12 Months calculation

That just involved adjusting the math.

See attached.

• ###### 4. Re: Rolling 12 Months calculation

Thanks Joe, Great Help