Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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]
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
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
Thank you. It works perfect.