Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Can you provide some more information about your requirements... i.e. few lines of sample data and little more clarification on your requirements...
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
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..
Hi Malish,
I'll try this tonight or on Monday and I tell you.
Thanks again
JAG
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.