How to get an extra 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 Ref, 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 taken in to the consideration for the calculation for every year, from that quarter.

can any one help me on this.

Regards
John

[marcus_sommer]

I suggest that you used a crosstable-load The Crosstable Load to get a normal data-table and used then within a pivot set analysis expression to get your wanted results (which are not quite clear to me):

crosstable:

crosstable(zone, value, 2)

Load Firm, Quarter, NZ, NY, CN, WD From YourData;

pivot:

sum({< Firm = {'TE', 'Ref'}>} value) / 2

- Marcus

[settu_periasamy]

Hi,

May be try this,

Directory;
A:
LOAD Firm, NZ, NY, CN, WD, Quarter,Rangesum(NZ,NY,CN,WD) as Sum
INLINE [
     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
];
Left Join(A)
Load Quarter,Sum as Sum1  resident A where Firm='Ref';
Final:
Load Firm,NZ,NY,CN,WD,Quarter,Rangesum(Sum,Sum1)/2 as Average Resident A;
DROP Table A;

Table A;

[jagan]

Hi,

Try this script

Data_Temp:

CrossTable(Zone, Sales, 2)

LOAD Firm, Quarter,      NZ,      NY,      CN,      WD

FROM [Data.xlsx] (ooxml, embedded labels, table is Sheet1)

WHERE Firm <> 'Ref';

RefSales:

CrossTable(Zone, RefSales,2)

LOAD Firm AS Ref, Quarter,      NZ,      NY,      CN,      WD  

FROM [Data.xlsx] (ooxml, embedded labels, table is Sheet1)

WHERE Firm = 'Ref';

LEFT Join(Data_Temp) LOAD *

RESIDENT RefSales;

DROP TABLE RefSales;

Data:

LOAD *, RangeSum(Sales, RefSales)/2 AS AvgSales

RESIDENT Data_Temp;

DROP TABLE Data_Temp;

Hope this helps you.

Regards,

Jagan.