topic Re: BEST MONTH OF LAST 12 in QlikView

BEST MONTH OF LAST 12

sorrakis01 — Fri, 10 Oct 2014 22:57:14 GMT

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

Re: BEST MONTH OF LAST 12

MK_QSL — Sat, 11 Oct 2014 07:48:36 GMT

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

Re: BEST MONTH OF LAST 12

sorrakis01 — Sat, 11 Oct 2014 09:17:55 GMT

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

Re: BEST MONTH OF LAST 12

MK_QSL — Sat, 11 Oct 2014 09:57:40 GMT

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

Re: BEST MONTH OF LAST 12

sorrakis01 — Sat, 11 Oct 2014 15:37:07 GMT

Hi Malish,

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

Thanks again

JAG

Re: BEST MONTH OF LAST 12

sorrakis01 — Mon, 13 Oct 2014 11:31:06 GMT

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.