Calculate 5 years average

[Anonymous]

Hi Everyone,

I have a requirement to calculate 5 years average.

For example:

company inputs:

company A:

2014 = 5

2015 = 10

2016 = 15

company B

2012 = 10

2013 = 15

2014 = 20

company C

2009 = 20

2010 = 25

2011 = 30

company calculations (3-year average)

company A = 10

company B = 15

company C = 25

population calculations (only for companies with data from the past five years)

companies included:  A, B (at least one year for both companies was 2013 or later)

companies excluded:  C (all years are prior to 2013)

five year average = average of included companies = (10 + 15) / 2 = 12.5

LOAD * INLINE [

    RecNo, Date, Value, company

    1,     2014,  5,     A

    2,     2015,  10,    A

    3,     2016,  15,    A

    4,     2012,  10,    B

    5,     2013,  15,    B

    6,     2014,  20,    B

    7,     2009,  20,    C

    8,     2010,  25,    C

    9,     2011,  30,    C

];

Please advice on how to achieve this?

Thanks in advance

[pooja_prabhu_n]

Hi,

try using set analysis

vLast5Year=Year(YearStart(Today(),-5))

Expression=Avg(DISTINCT {<Date={'>=$(vLast5Year)'}>}  Value)

Thanks,

Pooja

[raman_rastogi]

Hi

Kindly See attached QVF.

Raman

[eduardo_dimperio]

Why just not put a Where clausule to filter company where date >year()-5?

[jonathandienst]

Perhaps something like:

Sum({<Company = {"$(=Max(Date) >= 2015)"}>} Aggr(Avg(Value), Company))

[Anonymous]

Thanks for the reply.

I want to exclude C from average calculation :  C (all years are prior to 2013)

company calculations (3-year average)

company A = 10

company B = 15

company C = 25

population calculations (only for companies with data from the past five years)

companies included:  A, B (at least one year for both companies was 2013 or later)

companies excluded:  C (all years are prior to 2013)

five year average = average of included companies = (10 + 15) / 2 = 12.5

[Anonymous]

Could you please send me script or qvw file. I am unable to open qvf file.

[raman_rastogi]

Hi Kavya

Try This

T1:

LOAD

    "Date",

    Value,

    Company

FROM [lib://Desktop/310245.xlsx]

(ooxml, embedded labels, table is Sheet1);

Left Join

Load

Distinct

Company,

Year_Flag

Where Year_Flag>0;

Load

Company,

If(Year(Today())- Date <=5,1,0)as  Year_Flag

Resident T1;

Expression - Avg(if(Year_Flag >0,Value))

Raman

[Anonymous]

Hi Raman,

It is working fine. Suppose company B has data for four years(at least one year for both companies was 2013 or later).

For each company i should consider latest 3 years data.

ex:Average of company B = 28.33 ( 50 +20+15)/3 . We need to consider latest 3 years (2015,2014,2013)

LOAD * INLINE [

    RecNo, Date, Value, company

    1,     2014,  5,     A

    2,     2015,  10,    A

    3,     2016,  15,    A

    4,     2012,  10,    B

    5,     2013,  15,    B

    6,     2014,  20,    B

    7,     2015,  50,    B

   8,     2009,  20,    C

   9,     2010,  25,    C

  10,     2011,  30,    C

];

[raman_rastogi]

Hi Kavya,

May be like this

if(Rank(Max({<Year_Flag={1} >}Date))<=3,Avg(Value))

Raman