Expression having sum of few avg based on where clause

[#1]

Hi Experts,

Please find below few sample records:

Year	String	Subfield(String,'-',5)	Ratio
2007	2007-Q1-3-4-YR1	YR1	2
2007	2007-Q1-2-4-YR1	YR1	3
2007	2007-Q1-2-5-YR2	YR2	4
2007	2007-Q1-7-4-YR3	YR3	1
2007	2007-Q1-2-5-YR3	YR3	2
2007	2007-Q1-9-4-YR2	YR2	5

Now I need to calculate the following:

= sum of (avg(ratio) where Subfield(CFYStr,'-',5)='YR1' + avg(ratio) where Subfield(CFYStr,'-',5)='YR2' +avg(ratio) where Subfield(CFYStr,'-',5)='YR3' )

I need this in the expression of a line chart. I am not able to do this.

Any help would be highly appreciated.

Thanks in advance.

Regards

[sunny_talwar]

May be create the subfield field in the script:

Table:

LOAD Year,

     String,

     SubField(String,'-',5) as Dimension,

     Ratio

FROM

[https://community.qlik.com/thread/186294]

(html, codepage is 1252, embedded labels, table is @1);

and then use the following expression: =Sum(Aggr(Avg(Ratio), Dimension))