Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
sorrakis01
Specialist
Specialist

BEST MONTH OF LAST 12

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

5 Replies
MK_QSL
MVP
MVP

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

sorrakis01
Specialist
Specialist
Author

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.

ThanksExample 12 months.jpg

MK_QSL
MVP
MVP

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
Specialist
Specialist
Author

Hi Malish,

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

Thanks again

JAG

sorrakis01
Specialist
Specialist
Author

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.