Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
sorrakis01
Valued Contributor

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
MVP
MVP

Re: BEST MONTH OF LAST 12

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

sorrakis01
Valued Contributor

Re: BEST MONTH OF LAST 12

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

MVP
MVP

Re: BEST MONTH OF LAST 12

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
Valued Contributor

Re: BEST MONTH OF LAST 12

Hi Malish,

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

Thanks again

JAG

sorrakis01
Valued Contributor

Re: BEST MONTH OF LAST 12

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.