How to create a avg field?

[#1]

Hi All,

I have a table with Firm and zones , the table represents the sale for each firm in different zones. now I want to calculate one more extra field with average of two firms ,In those two  firms one is constant, and another one is changing.
like                                                                                                                                                                                                                     

Firm NZ NY CN WD Quarter
TE 65 65 65 65 Q1
FE 39 39 39 39 Q1
BU 69 69 69 69 Q1
LT 20 20 20 20 Q1
ST 55 55 55 55 Q1
RV 84 84 84 84 Q1
LE 85 85 85 85 Q1
UX 65 65 65 65 Q1
AN 78 78 78 78 Q1
ZU 65 65 65 65 Q1
YD 72 72 72 72 Q1
OT 65 65 65 65 Q1
Ref 91 92 92 94 Q1
TE 65 65 65 65 Q2
FE 39 39 39 39 Q2
BU 69 69 69 69 Q2
LT 20 20 20 20 Q2
ST 55 55 55 55 Q2
RV 84 84 84 84 Q2
LE 85 85 85 85 Q2
UX 65 65 65 65 Q2
AN 78 78 78 78 Q2
ZU 65 65 65 65 Q2
YD 72 72 72 72 Q2
OT 85 84 78 95 Q2
Ref 94 95 96 95 Q2

here I                   

Need an extra field with the avg of every firm with Firm, like sum(TE+Ref)/2,sum(FE+Ref)/2…. For every Zone for every Quarter.
means for every quarter the Ref no has to be talen from that quarer it self.

can any one help me on this.

Regards
John

[sunny_talwar · Accepted solution]

Try this:

Table:

LOAD Firm,

    NZ,

    NY,

    CN,

    WD,

    Quarter

FROM

Community_173262.xlsx

(ooxml, embedded labels, table is Sheet1);

Join(Table)

LOAD NZ as NZ_Ref,

    NY as NY_Ref,

    CN as CN_Ref,

    WD as WD_Ref,

    Quarter

Resident Table

Where Firm = 'Ref';

NewTable:

LOAD Firm,

    NZ,

    NY,

    CN,

    WD,

    Quarter,

    (NZ + NZ_Ref)/2 as Avg_NZ,

    (NY + NY_Ref)/2 as Avg_NY,

    (CN + CN_Ref)/2 as Avg_CN,

    (WD + WD_Ref)/2 as Avg_WD

Resident Table;

DROP Table Table;

Output:

HTH

Best,

Sunny

[#3]

Thanks Sunny. it works.