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

    Calculation with effective or rolling dates

    . Oruvan

      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 RepSale DateSale Amount
      John 1/15/20112000


      Expected Answer :


      Days Since First SalesSales RepCount of Repeat Sales after first Sale
      30 daysJohn 1
      60 daysJohn 2
      Charles 3


      Thanks in Advance.

        • 1. Re: Calculation with effective or rolling dates
          Tracy Rodgers

          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!



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

            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"