BEST MONTH OF LAST 12

[sorrakis01]

Hi,

Unable to resolve this problem.

I need to know which is the best month of the last 12 in sales, I choose Year or Month according suits me. Example format:

Choose selection boxes:

Year -> 2014   Month -> September

Pivot Table:

Product | Sales This Month | Top Month |

Product A              50000       August 2014

Product B             10000        February 2014

Product C            150000       December 2014

Product D             20000         July 2014

Thanks

[MK_QSL]

Can you provide some more information about your requirements... i.e. few lines of sample data and little more clarification on your requirements...

[sorrakis01]

hi Manish,

We have more than 50 sellers with sales every day. I need to Know what's the best month of the last 12 months, in only one expression in the same table for each one. 

I can't upload the document for the sensitive data, but i upload a picture.

Thanks

[MK_QSL]

OK... I will give you another example based on that you can work out ....

Consider you have Sales data for different customers over a period of times. Let's say we have below fields..
Customer, Sales, InvoiceDate

Our objective is to find out Month having Maximum Sales over Last 12 Months period...

Kindly follow below steps

1) either create a master calendar or create below field in you sales table

Date(MonthStart(InvoiceDate),'YYYYMM') as MonthYear

2) Now create a straight table...

Dimension

Customer

Expressions

=Max(Aggr(SUM({<MonthYear = {">=$(=Date(MonthStart(Today(),-11),'YYYYMM'))"}>}Sales),MonthYear))

=Date(FirstSortedValue(MonthYear,-Aggr(SUM({<MonthYear = {">=$(=Date(MonthStart(Today(),-11),'YYYYMM'))"}>}Sales),MonthYear)),'YYYYMM')

Let me know if it is still not working for you..

[sorrakis01]

Hi Malish,

I'll try this tonight or on Monday and I tell you.

Thanks again

JAG

[sorrakis01]

Hi Manish,

Firtsly thanks for your answer and your time, your example works but it's a problem. You use the date Today() and whats up If I need to use another date of the selection values. Ex: If I selected Year 2013, your example doesn't work, what do you think about it? it's possible other solution?

Thanks.