max sales of last 6 months

[#1]

Hello,

Can you help me with my issue? I have a database like this:

sku	date	qnty	sales
a	01.01.2012	10	30
b	02.01.2012	20	60
c	02.01.2012	5	15
a	05.01.2012	30	90
a	10.01.2012	10	30
b	03.02.2012	30	60
c	03.02.2012	50	150
a	25.02.2012	20	60
b	07.03.2012	10	30
b	10.03.2012	45	135
a	10.03.2012	15	60
c	15.03.2012	10	30

I want to get straight table, which will show me month in which was maximum sales during last 6 months and I want to see sum of this month too. This table must include all maximums for all sku. For example: if I choose  march 2012, it will show max sales since october 2011 and if I choose december 2011, it will show since july 2011.

I try to use something like this

max(aggr(sum({$<[Month Year]={">=$(=AddMonths(Max([Month Year]),-5))<=$(=AddMonths(Max([Month Year]),-0))"},month=>} [sales]),year,month,[sku]))

but it worked only if i didn't choose any month.

Do you have any ideas?

Thanks for your help, Bill.

some information:

month, year - from another table like this:

Date	month	quarter	year
01.01.2012	january	Q1	2012
02.01.2012	january	Q2	2012
03.01.2012	january	Q3	2012
04.01.2012	january	Q4	2012

Month Year from script:

MonthName(DATE(Date, 'DD/MM/YYYY')) AS [Month Year]

[fosuzuki · Accepted solution]

Try inserting the same set analysis expression in the outer max() function:

max({$<[Month Year]={">=$(=AddMonths(Max([Month Year]),-5))<=$(=AddMonths(Max([Month Year]),-0))"},month=>} aggr(sum({$<[Month Year]={">=$(=AddMonths(Max([Month Year]),-5))<=$(=AddMonths(Max([Month Year]),-0))"},month=>} [sales]),year,month,[sku]))

The concept is that the max() function also has to have the set analysis expression to override the current $ when you select any month.

Hope this helps you.

Regards,

Fernando

[#3]

Thank you. It works perfect.