How to calculate previous 12 months avg

[#1]

Dear All,

I am preparing pivot table which has avg sale calculation..

I require when i select any date in pivot table avg scalculation should calculate avg from current selection upto 12 months backward.

Ex if i select apr 2009 then avg calculation should pick up avg from 2008 apr till 2009 apr

Can anyone help me in this calculation.

Thanks in advance..

[sparur]

Hi, RajTechno.

For example here is the solution:

Sum({1< Date2= {"$(='<='&Only(Date2)&'>'&Only(date(addmonths(Date2,-13),'MMM-YYYY')))"} >} NoOfTickets)

where Date2 is a Field with date format 'MMM-YYYY' .

P.S. replace the function sum () on Avg ()

[#3]

hi i tried to use ur solution but it is not working out.

my expression is: Sum({1< custinvoicetrans.INVOICEDATE= {"$(='<='&Only(custinvoicetrans.INVOICEDATE)&'>'&Only(date(addmonths(custinvoicetrans.INVOICEDATE,-13),'MMM-YYYY')))"} >} custinvoicetrans.LINEAMOUNT)

Problem i faced is: It shows only current month sum only.

it doesn't calculate for previous 12 months.

thanks for ur time...

[sparur]

where you choose the date? what field?

Do you have a Calendar Table with [MonthYear] Field? If not, you should create it in LOAD script.

Example:

calendar:

LOAD custinvoicetrans.INVOICEDATE,
 year(custinvoicetrans.INVOICEDATE) as Year,
month(custinvoicetrans.INVOICEDATE) as Month,
 Date(Monthstart(custinvoicetrans.INVOICEDATE), 'MMM-YYYY') as MonthYear
RESIDENT custinvoicetrans;

try remove a set identifier "1" and try use a field [MonthYear] in set analysis.

your expression would look like this:

Sum({< MonthYear= {"$(='<='&Only(MonthYear)&'>'&Only(date(addmonths(MonthYear,-13),'MMM-YYYY')))"} >} custinvoicetrans.LINEAMOUNT).

P.S. or attach your application to the analysis of this problem

[#5]

Dear S Parur,

thanks for your reply

I used MonthYear created at script level but it is giving me only currentmont and previous year month value.

What i wanted was when i select year 2009 month : Apr.

the calculation should claculate through apr 2009 mar 2009 feb 2009 jan 2009 dec 2008 nov 2008 oct 2008 sep 2008 aug 2008 jul 2008 jun 2008 may 2008 apr 2008

sum up all these values and give me total.

thanks for ur reply

[sparur]

Dear, RajTechno

Look at the QV document in attachment.