pivottable expression

[Anonymous]

Hi

I have a problem with pivot table expression, my table is something like this:

ItemNumber	101_Q	101_A	102_Q	102_A	Total_Q	Total_A	104_Q	104_A	201_Q	201_A	202_Q	202_A	203_Q	203_A
A100	11	11	12	8	23	19	3	0	3	0	12	0	13	0
A101	13	12	13	13	26	25	11	0	4	0	2	0	11	0
A102	2	3	2	9	4	12	6	0	12	0	6	0	12	0
A103	7	10	9	12	16	22	14	0	8	0	15	0	2	0

Ive used CrossTable I would like to calculate 201_A , 202_A and 203_A based on below formula:

201_A=201_Q * (Total_A / Total_Q) abd so on.

my qv file is attached, how can i do that?

[sunny_talwar]

What would be the calculation for 202_A and 203_A?

[Anonymous]

Hi Sunny Talwar

202_A= 202_Q * (Total_A / Total_Q)

203_A =203_Q * (Total_A / Total_Q)

[sunny_talwar]

Are you open to some script changes? not too complicated... just adding a new field which will store the number before '_' in the itemnumber field

[Anonymous]

sorry can you look at my qv file and share it, thanks.

[sunny_talwar]

I can, all I am saying is that are you okay with making script changes?

[Anonymous]

yes I can

[sunny_talwar · Accepted solution]

Check the attached

[Anonymous]

sorry could you send the qv file. thank you so much

[Anonymous]

wow what a wonderful solution. thank you so much