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
      John1/1/20111000
      John 1/15/20112000
      John3/15/20113000
      John8/15/201150
      Diaz2/1/2011300
      Diaz4/1/2011400
      Diaz5/1/2011500
      Charles3/1/2011100
      Charles3/15/2011200
      Charles3/18/2011300
      Charles4/28/20111000

       

      Expected Answer :

       

      Days Since First SalesSales RepCount of Repeat Sales after first Sale
      30 daysJohn 1
      Charles2
      60 daysJohn 2
      Diaz1
      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!

           

          -Tracy

          • 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"