Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
see the attched file
hope this helps
Thank you very much for your help, Jason!
Your example works great.