last 12 Months Avg Sales ?

[#1]

Hi All,

I need to get Last 12 Months Avg Sales, Including Selected Month..Remaining months go  back dynamically

Ex: If Select Year-2014, Month-Jul

Sample Output for One Month & Year : 279.166

Load * Inline [

Year, Month, Sales

2014, Sep, 200

2014, Aug, 300

2014, Jul, 100

2014, Jun, 500

2014, May, 200

2014, Apr, 300

2014, Mar, 100

2014, Feb, 200

2014, Jan, 600

2013, Dec, 150

2013, Nov, 250

2013, Oct, 50

2013, Sep, 700

2013, Aug, 200

2013, Jul, 600

2013, Jun, 300

2013, May, 200

]

Regards,

Helen

[deepakqlikview_123]

RangeAvg(AGGR(Sum({$<Year={$(='">=" & Year(AddMonths(Max(Date),-12)) & '"')},

Month={$(='">=" & Month(AddMonths(Max(Date),-12)) & '"')}

>}Sales),Month))

[ecolomer]

Here you have an example for thid

[jyothish8807]

Hi Helen,

try to create a new field in script:

date(Month & Year,'MMM YYYY') as NewYear

Then create a variable:

vCurrentDate = Max(NewYear)

Then a expression:

Avg({<[NewYear]={">=$((vCurrentDate)-12) <=$(vCurrentDate)"}>}Sales)

Regards

KC

[Kushal_Chawda]

Please find the attached file.

Regards,

Kush

[#6]

If I was not selected any Month & Year, By Default it Show Max Month & Year

[ecolomer]

Ok, see the file

[Kushal_Chawda · Accepted solution]

Please see the attached