Last Three Months Calc

[#1]

Hi,

I am creating a Pivot Table chart which will show certain Calculation the last three months.

This report will be static, say if we are in the Month of - FEB then the Report should show NOV-DEC-JAN

Nov	Dec	Jan

In the next month ie. MAR the Report should show DEC-JAN-FEB.

Dec	Jan	Feb

The issue I am facing is with the Year, since it is 2014 and months needed in the report are of 2013 as well, I am stuck !

Please help me on the same.

Thanks.

[#2]

hi try this

sum({$<Month={'>=$(=max(Month-2))<=$(=max(Month))'}>}Value)

[#3]

hi

for year wise-

sum({$<Month={'>=$(=max(Month-2))<=$(=max(Month))'}, year={'=$(=max(year-1))'}>}Value)

[#4]

Hi Priyanka,

Use AddMonths function. Hope this is the right solution for your query.

AddMonths(startdate, n , [ , mode] )

Returns the date occurring n months after startdate or, if n is negative, the date occurring n months before startdate.

By specifying a mode (0 if omitted) the date is set to either the unmodified day of the specified month (mode=0) or the calculated day as derived from the end of the month (mode=1).

Examples:

addmonths ('2003-01-29',3) returns '2003-04-29'

addmonths ('2003-01-29',3,0) returns '2003-04-29'

addmonths ('2003-01-29',3,1) returns '2003-04-28'

addmonths ('2003-01-29',1,0) returns '2003-02-28'

addmonths ('2003-01-29',1,1) returns '2003-02-26'

addmonths ('2003-02-28',1,0) returns '2003-03-28'

addmonths ('2003-02-28',1,1) returns '2003-03-31'

[#5]

Egxample:

=Date#(month( addmonths ('2013-02-07',-3)),'DD/MM/YYYY')

[er_mohit]

Try this

sum({<Date={'>=$(=Monthstart(Max(Date),-3))<=$(=monthend(Max(Date),-1))'},YearField=,Monthfield=,QuarterField=>}SaleField)

or

Sum({<Date={'>=$(=Addmonth(Makedate(max(YearField),Max(Monthfield)),-3))'},YearFiled=,Monthfield=>}Amount)

hope it helps

[Sokkorn]

Hi,

In the script, assign a consecutive number for each month, a unique ID. Here is an example

(Year(DateField) – 1)  * 12 + Num(Month(DateField)) as MonthID

In your expresion:

=Sum({1<MonthID = {">=$(=Max(MonthID)-3)<=$(=Max(MonthID))"},Month = ,Quarter = ,Year = >} Sales)

Regards,

Sokkorn

[manojkulkarni]

sum({<MY ={">=$(=Date(addmonths(Max(MY), -2), 'MMM-YY')) <=$(=Date(addmonths(Max(MY), 0),  'MMM-YY'))"} >}

measure_field)

[#9]

Hi all,

Thanks for all your reply.

Each of your solution is pretty much near, but the resultant months I am getting is Oct-Nov-Dec, whereas my chart need to display Nov-Dec-Jan.

I think that is because of the change in Year Nov and dec are of 2013 and Jan is of 2014.

Kindly help me to solve this.

Thanks !

[sundarakumar]

PFA. This should help,

I have added calculated dimension for this.

Hope this helps.

-sundar