average monthly sales for the last 12 months

[bilionut]

Hello,

In a Pivot table I have to calculate the average monthly sales for the last 12 months for each customer, having these two tables and considering the first transaction date for each customer.

[sales]:

customer,net_sales,date

AAA,100,2/13/2011

AAA,100,6/14/2011

AAA,100,7/25/2011

AAA,100,9/13/2011

AAA,100,12/17/2011

AAA,100,4/14/2012

BBB,100,10/25/2011

BBB,100,11/16/2011

BBB,100,12/27/2011

BBB,100,4/18/2012

[customer]:

customer,first_transaction_date,

AAA,4/13/2008,

BBB,10/25/2011

For AAA customer the average is 500/12 $/month because I have to exclude transactions older than 12 months.

For BBB the average is 400/7 $/month (sales between first transaction date and current month)

Any help will be greatly appreciated,

Ionut

[Jason_Michaelides · Accepted solution]

Hiya,

See attached.  I have created 2 variables - vLastYear (today last year) and vMonthDiff (months between today and first transaction date).  Using a combination of set analysis and RangeMin() you can achieve what you wanted.

Thanks must go to Rob Wunderlich for his MonthDiff solution (http://community.qlik.com/thread/47580?start=0&tstart=0)

Hope this helps,

Jason

[SunilChauhan]

see the attched file

hope this helps

[bilionut]

Thank you very much for your help, Jason!

Your example works great.