Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
What about this:
=Avg({<Date ={">=$(=Date(AddMonths(Max(Date), -2), 'DD/MM/YYYY'))<=$(=Date(AddMonths(Max(Date), -1), 'DD/MM/YYYY'))"}>} Sales)
Nope,
It is not working
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)
Yeah, It is not working.
Where are you using this expression and what is the expected output (based on your sample data)?
Hi,
I have to get last 3 or N months Avg value only.
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)
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
.........];
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.