# New to QlikView

Discussion board where members can get started with QlikView.

New Contributor II

## average monthly sales for the last 12 months

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

Labels (2)

• ### Scripting

1 Solution

Accepted Solutions
Honored Contributor II

## Re: average monthly sales for the last 12 months

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

3 Replies
Honored Contributor II

## Re: average monthly sales for the last 12 months

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

Esteemed Contributor

## Re: average monthly sales for the last 12 months

see the attched file

hope this helps

New Contributor II

## Re: average monthly sales for the last 12 months

Thank you very much for your help, Jason!