topic Calculate 5 years average in QlikView

Calculate 5 years average

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Calculate 5 years average

pooja_prabhu_n — Tue, 07 Aug 2018 11:17:57 GMT

Hi,

try using set analysis

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

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

Thanks,

Pooja

Re: Calculate 5 years average

raman_rastogi — Tue, 07 Aug 2018 11:38:56 GMT

Kindly See attached QVF.

Raman

Re: Calculate 5 years average

eduardo_dimperio — Tue, 07 Aug 2018 12:27:54 GMT

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

Re: Calculate 5 years average

jonathandienst — Tue, 07 Aug 2018 12:45:43 GMT

Perhaps something like:

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

Re: Calculate 5 years average

Anonymous — Tue, 07 Aug 2018 13:22:11 GMT

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

Re: Calculate 5 years average

Anonymous — Tue, 07 Aug 2018 13:26:26 GMT

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

Re: Calculate 5 years average

raman_rastogi — Tue, 07 Aug 2018 14:51:46 GMT

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

Re: Calculate 5 years average

Anonymous — Wed, 08 Aug 2018 06:43:58 GMT

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

];

Re: Calculate 5 years average

raman_rastogi — Wed, 08 Aug 2018 07:23:48 GMT

Hi Kavya,

May be like this

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

Raman

Re: Calculate 5 years average

Anonymous — Wed, 08 Aug 2018 09:25:02 GMT

It is not working...