2 Replies Latest reply on Nov 10, 2013 8:54 AM by Ivan Monnier

    Daily or weekly calculation vs end on month value

    Ivan Monnier

      Hello,

       

      I am not really sure on how to formulate my request.

      I am calculating a customer call rate by dividing the number of customer calls by the customer base.

      All my data is in a single table, I use a filed to separate calls and customer base.

      To have a monthly value for the calls, I just sum the number of calls.

      To calculate my monthly customer base, I count the disctinct Customer IDs.

      That works fine for a monthly follow-up.

      Monthly_call_rate.JPG.jpg

       

      My problem is, I need to have weekly call rates.

      The date for the calls are each day of any month.

      The date used to calculate the customer base is the last day of each month.

       

      I tried to use a WINDOWSUM function.

      That works fine if I filter on one month only.

      Weekly_call_rate_1month.JPG.jpg

      You can see that, on the left, my customer base is only calculated for the last week of the month.

      On the right, that the customer base calculated with a WINDOWSUM wotks for each week, and my call rate is true.

       

      Unfortunately, that doesn't work anymore if I display more than a month.

      Weekly_call_rate_2month.JPG.jpg

      The customer base is the cum of the 2 month.

       

      How can I have a call rate which divides the number of calls of a given week by the number of cutomers of the end of the same month?

       

      Your help will be most welcome .

      I attach the workbook with sample data.