Rolling Month Trail

[abhaysingh]

Hi pls chk below Exp

=sum({< [Calender Month]= {'>=$(=month(AddMonths(max([Calender Month]),-3)))<= $(=month(max([Calender Month])))'}>} Quantity* UnitPrice)

this expression is not working..

pls tell me where is the error??

i want MaxMonth to last three month Trend

thanks

[jvitantonio]

Hi, please remove function "month" from your set analysis and use your Date field

=sum({< [Date]= {'>=$(=AddMonths(max([Date]),-3))<= $(=max([Date]))'}>} Quantity)

JV

[jonathandienst]

The expression syntax looks correct, so the problem is somewhere in your data. Month() returns the short month name (as in Jan, Feb, Mar according to your MonthNames environment variable). Is Calender Month in that format as well?

If Calendar Month is the same format as Month(), then your AddMonths expression will not work correctly as it expects a date, not a month, as the first parameter. Do you have a Date field in your data? Use that in your expression.

Is Calendar Month the field you are making the selection on? Or do you have other date related fields where the actual selections occur? You will need to override these selections in the set expression. Lets say you have 2 fields, Year and Month, then use

=sum({< [Calender Month]= {'>=$(=month(AddMonths(max([Calender Month]),-3)))<= $(=month(max([Calender Month])))'}, Month, Year>} Quantity* UnitPrice)

If you select January, so you want to see November and December of the previous year? You will need a proper date field to handle this.