4 Replies Latest reply on Nov 26, 2013 10:36 AM by Jeff Stanley

# Month's since Start and annualized amount

I would like to calculate the number of months since a "firm" has been a customer, essentially the number of months from today back to the first transaction. This number would then be used to filter out those who have been customers for less than 6 months, and calculate a projected 12 month total based on the average monthly sales for the remainder. I have attached a sample

• ###### 1. Re: Month's since Start and annualized amount

See if the attached will get you on your way.  I created a MIN(Trade Date) field, and used that to calculate the # of months from today's date: DATEDIFF('month',[MIN Trade Date], today()).  The "Rank" sheet shows example results of this calc.

You could then filter based on this field.  I added a continous filter based on this field to exemplify this.

• ###### 2. Re: Month's since Start and annualized amount

how would I use the commission amount and # of months number to calculate an average per month? I get a cannot mix aggragate and non aggragate error?

• ###### 3. Re: Month's since Start and annualized amount

I am not clear on what you are trying to accomplish, but the # of months is an aggregate, so any calculation using this field must have all aggregated fields.  If you were dividing commission by # of months, you could use:

sum([Commission])/[# of Months since first transaction]  // assuming a sum is the correct aggregation

OR if summing the commission is not what you want, possibly:

attr([Commission])/[# of Months since first transaction]

But again, I'm not clear on how this helps get you to an average per month.  If you simply use the # of months to filter the data, you can then just use an AVG(Commission) to get the results after the filter is applied to your data.

• ###### 4. Re: Month's since Start and annualized amount

that was it, thanks the "sum" function was what I needed.