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?

[Anonymous]

Mr Sunny Talwar

One more question! how to use background expression in pivot table, for example, I want to use light gray for 101-Q to 104-A Columns.

[kakani87]

Something like this?

Backgroung exp as - if(match (Transaction,'101_Q','101_A','102_Q','102_A','Total_Q','Total_A','104_Q','104_A'),RGB(192,192,192))

[Anonymous]

That is right.

[Anonymous]

sorry again

in my pivot table like below:

ItemNumber	Transaction	101_Q	101_A	102_Q	102_A	Total_Q	Total_A	104_Q	104_A	201_Q	201_A
A100		11	11	12	8	23	19	0	0	3	2
A101		13	12	13	13	26	25	0	0	4	4
A102		2	3	2	9	4	12	0	0	12	36
A103		7	10	9	12	16	22	0	0	8	11
جمع		33	36	36	42	69	78	0	0	27	53

as you see the 104_Q and 104-A for all item number is zero, thus it should be removed; how to do that?

[kakani87]

In presentation tab try

check -> Supress Zero-Value

[Anonymous]

It does not work

[Anonymous]

thanks it works

[Anonymous]

but why my it affected my background color?

[Anonymous]

you know for example in some columns zero value does not get background color.