topic Re: Re: Re: last 12 Months Avg Sales ? in QlikView

last 12 Months Avg Sales ?

Sun, 26 Oct 2014 04:53:15 GMT

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

Re: last 12 Months Avg Sales ?

deepakqlikview_123 — Sun, 26 Oct 2014 05:53:31 GMT

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

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

>}Sales),Month))

Re: last 12 Months Avg Sales ?

ecolomer — Sun, 26 Oct 2014 05:57:53 GMT

Here you have an example for thid

Re: last 12 Months Avg Sales ?

jyothish8807 — Sun, 26 Oct 2014 06:01:11 GMT

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

Re: last 12 Months Avg Sales ?

Kushal_Chawda — Sun, 26 Oct 2014 06:11:15 GMT

Please find the attached file.

Regards,

Kush

Re: last 12 Months Avg Sales ?

Sun, 26 Oct 2014 06:31:17 GMT

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

Re: Re: last 12 Months Avg Sales ?

ecolomer — Sun, 26 Oct 2014 06:35:41 GMT

Ok, see the file

Re: Re: Re: last 12 Months Avg Sales ?

Kushal_Chawda — Sun, 26 Oct 2014 07:42:27 GMT

Please see the attached