Last N month values

[Anonymous]

Hi All,

I am looking for last N months Avg values in the chart. I am using below expression

=AVG({<Date ={">=$(=Date(addmonths(Max('$(vyearmonth)'), -2), 'MMM-YYYY')) <=$(=Date(addmonths(Max('$(vyearmonth)'), -1),  'MMM-YYYY'))"} >} Sales)

which is not giving any values

My data looks like below

load * Inline

[

Date,Sales

01/05/2016,20

02/05/2016,10

03/05/2016,20

04/05/2016,20

05/05/2016,30

.........];

vyearmonth

=MONTHNAME(date#(Date,'DD/MM/YYYY'))

Kindly let me know what and where I am missing

[sunny_talwar]

What about this:

=Avg({<Date ={">=$(=Date(AddMonths(Max(Date), -2), 'DD/MM/YYYY'))<=$(=Date(AddMonths(Max(Date), -1),  'DD/MM/YYYY'))"}>} Sales)

[Anonymous]

Nope,

It is not working

[sunny_talwar]

Have you made all these changes?

=Avg({<Date ={">=$(=Date(AddMonths(Max(Date), -2), 'DD/MM/YYYY'))<=$(=Date(AddMonths(Max(Date), -1),  'DD/MM/YYYY'))"}>} Sales)

[Anonymous]

Yeah, It is not working.

[sunny_talwar]

Where are you using this expression and what is the expected output (based on your sample data)?

[Anonymous]

Hi,

I have to get last 3 or N months Avg value only.

[Anonymous]

Hi

I am hoping  vyearmonth is a FIELD ?

or

how do you store the  'vyearmonth' ?

vyearmonth

=MONTHNAME(date#(Date,'DD/MM/YYYY'))

1)check your  vyearmonth field's format MMM YYYY or  MMM-YYYY

and try without ' '  for the field  vyearmonth

2)=AVG({<Date ={">=$(=Date(addmonths(Max((vyearmonth)), -2), 'MMM-YYYY')) <=$(=Date(addmonths(Max((vyearmonth)), -1),  'MMM-YYYY'))"} >} Sales)

[stigchel]

Are you sure that the field Date is an actual date formatted field? From your example load script this is not clear. To make sure, check if your set statements at the top of the load script contains this

SET DateFormat='DD/MM/YYYY';

or use

Load date#(Date,'DD/MM/YYYY') as Date,Sales;

load * Inline

[

PreDate,Sales

01/05/2016,20

02/05/2016,10

03/05/2016,20

04/05/2016,20

05/05/2016,30

.........];

[Anonymous]

Hi Allu,

vyearmonth is variable I was using in the pivot table.

As I was using in the Inline I have used Date# in the variable to get date format. I have tried your option 2 but still I am not getting any values.