Last 6 Months

[paulwalker]

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

[Sokkorn]

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

[paulwalker]

Hi Sokkorn,

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

[kumarnatarajan]

PFA

[PrashantSangle]

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')

Or

Just use ,

this expression

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

Regards

[paulwalker]

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

[PrashantSangle]

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,

[sasikanth]

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