topic Last 6 Months in QlikView

Last 6 Months

paulwalker — Sat, 14 Jun 2014 03:32:25 GMT

Hi Community,

PFA,

I want to show last 6 months data based on current selection.

I did something but it's not showing properly,

any thing wrong my expression..

Thanks in Advance..

Re: Last 6 Months

Sokkorn — Sat, 14 Jun 2014 03:49:47 GMT

Hi Paul,

I would suggest add new field for month sequence in your calendar table

After that your set analysis should be

=Sum({<MonthSeq = {">=$(=Max(MonthSeq)-6)<=$(=Max(MonthSeq))"}>} Qty)

Regards,

Sokkorn

Re: Last 6 Months

paulwalker — Sat, 14 Jun 2014 04:14:19 GMT

Hi Sokkorn,

It's not working, i want 6 months based on current selection

Re: Last 6 Months

kumarnatarajan — Sat, 14 Jun 2014 05:36:31 GMT

PFA

Re: Last 6 Months

PrashantSangle — Sat, 14 Jun 2014 06:22:14 GMT

Hi,

You are almost there but only reason that your expression is failling due to date Format.

By Default it is taking as MM/DD/YYYY

There fore it is showing wrong output.

You can change your variable expression in to

Date(AddMonths(Max(SalesDate),-6),'DD/MM/YYYY')

Just use ,

this expression

Sum({<SalesDate={">$(=Date(MonthStart(AddMonths(SalesDate,-6)),'DD/MM/YYYY')) <=$(=max(SalesDate))"}>}Qty)

Regards

Re: Last 6 Months

paulwalker — Sat, 14 Jun 2014 06:27:23 GMT

Hi Max,

That is ok, but some values show's 7, but i want last 6 months onlyy

01/10/2011

01/11/2011

01/12/2011

see this date's

Re: Last 6 Months

PrashantSangle — Sat, 14 Jun 2014 06:41:35 GMT

Hi,

But that was correct,

I checked data for Sales Date=01/12/2011

Then for last 6 month It is between 01/06/2011 to 01/12/2011

Which is correctly given by our expression.

When I select Sales Date=01/12/2011,

Then It showing me 7 Date which are

11/06/2011 -It is showing because it is greater than min range which is 01/06/2011

01/07/2011

01/08/2011

01/09/2011

01/10/2011

01/11/2011

01/12/2011 - It showing because it is our max selection which is 01/12/2011

Suppose if you have one more entry between this range then it showing you more than 7

I hope you understand what i want to say

Regards,

Re: Last 6 Months

sasikanth — Sat, 14 Jun 2014 06:44:18 GMT

HI paul

go through the following link where u will found , rolling n period details

Calculating rolling n-period totals, averages or other aggregations

Hope it helps you

Regards

Sasi