add calculated field

[upaliwije]

Hi,

I have in my excel sheet used for QV documents the following Fields (except new field)

class  Gross Profit   Direct Exp   Direct Income   New Field

A       100                 30              10                    80

B       200              100                50                  150

C        300               150              100                250

I want to create this new field (Gross Profit-Direct Exp+Direct Income) hilighted above either in load script or expression and my output report(pivot table) should look like above.

Pls help to fullfil my requirement

[sunny_talwar]

Have you tried this:

LOAD class,

          [Gross Profit],

          [Direct Exp],

          [Direct Income],

          [Gross Profit] - [Direct Exp] + [Direct Income] as [New Field]

FROM Excel;

[upaliwije]

Tks Sunny

In fact my data is in following format and I am load ing them in cross Table mode

                        A         B         C

Gross Profit      100      200     300

Direct Exp         300    100     150

Direct Income     10      50      100

Is it possible to do what you suggest under cross table load ?

[sunny_talwar]

Would you be able to share your script?

[upaliwije]

Sorry for the delay

CrossTable(CLASS, AMOUNT, 4)

LOAD *

FROM

G:\PRD_PROFIT\*EXP.xls

(biff, embedded labels, table is HO$);

[sunny_talwar]

You hid all the field names by using *, that is what I needed to see. Can you put down all the fieldnames instead of using a *

[upaliwije]

Here is my full script

CrossTable(class, amount, 4)

LOAD BCOD,

     Category,

     Description,

     A,

     B,

     C,

     D

FROM

G:\PRD_PROFIT\AAEXP.xls

(biff, embedded labels, table is HO$);

[upaliwije]

I have put following under Description

Gross Profit     

Direct Exp       

Direct Income

[upaliwije]

I have added my sample QV documents. Pls someone help me add a column (net Profit ) to my pivot table

CLASS	Description	GROSS PROFIT	Direct Expenses	Expense1	Expense2	Direct  Income	NET Profit
A		2,956,865	1,177,685	1,545,008	1,588,282	293,962	-1,060,148
B		413,135	90,591	118,847	122,176	25,631	107,153
C		388,048	362,365	475,387	488,702	76,364	-862,042
D		4,601,248	362,365	475,387	488,702	128,684	3,403,478

http://

[sunny_talwar]

Like this?