2 Replies Latest reply on Jul 23, 2012 3:35 PM by . Oruvan

# Calculation with effective or rolling dates

I am trying to compute repeat sales for the sample data below. The question is how many sales reps did a repeat sale within 30 days after their first sale and also 60 days after their first sale. It will be great if I can use parameter to control the 30 or 60 days variable.

The data is shown below (Excel file also attached).

 Sales Rep Sale Date Sale Amount John 1/1/2011 1000 John 1/15/2011 2000 John 3/15/2011 3000 John 8/15/2011 50 Diaz 2/1/2011 300 Diaz 4/1/2011 400 Diaz 5/1/2011 500 Charles 3/1/2011 100 Charles 3/15/2011 200 Charles 3/18/2011 300 Charles 4/28/2011 1000

 Days Since First Sales Sales Rep Count of Repeat Sales after first Sale 30 days John 1 Charles 2 60 days John 2 Diaz 1 Charles 3

• ###### 1. Re: Calculation with effective or rolling dates

Hi Oruvan,

One way to do this is the following. Place Sales Rep and the exact date of Sales Date on the rows shelf. Then, create a float parameter with a list of 30 and 60 (in my example this is called 30 or 60 days).

Then, create a calculated field similar to the following (Calculation1):

if attr([Sales Date])<=dateadd('day', [30 or 60 Days ], window_min(min([Sales Date]))) then running_sum(count([Amount]))-1 else NULL end

Place this on the view. Right click and select Compute Using-->Pane (Down). Right click again and select Filter... Choose the Special box and select Non-null values. Click OK.

Right click on the parameter and select Show Parameter Control.

To not show the dates, place the Sales Date field on the level of detail shelf.

Then, create a calculated field similar to the following:

if (first()==0) then window_max(([Calculation1]), 0, iif(first()==0, last(), 0)) end

Place this calculation on the view. Right click on it and select Compute Using-->Sales Date. Remove Calculation1 from the view.

Hope this helps!

-Tracy

• ###### 2. Re: Calculation with effective or rolling dates

Tracy, thanks a ton. If I may ask a follow-on question, extending the same logic I was trying to compute the sum of repeat sales for 30 or 60 days. The index seems to be off by 1, any help is greatly appreciated.

I have attached the packaged workbook, the worksheet in question is "Sum of Repeat Sales"