Calculation in Pivot Table

[upaliwije]

I have following straight table in my QV document. I have created Column 1 2 4 and want to write expression to create column 3 ,5,6 and 7

My expression for Column No 2 is Sum(Actual) and for  4 is Sum(Budget)

Pls help me to write the expression and my excel sheet is also attached herewith

1	2	3	4	5	6	7
DESCRIPTION	ACTUAL	Ratio	BUDGET	Ratio	Revised	VARIENCE
SALES	6,695,821		12,659,000		6,695,821	0
RETURNS	2,349,506	35	2,491,693	20	1,317,950	1,031,556
NET_SALES	4,346,315	65	10,167,307	80	5,377,871	-1,031,556
COMM_INCOME	367,508	5	278,270	2	147,187	220,321
TOTAL_REVENUE	4,713,823	70	10,445,577	83	5,525,059	-811,235
COMM_EXPENSES	1,274,026	19	1,673,870	13	885,373	388,653
GROSS_PROFIT	3,439,797	51	8,771,707	69	4,639,686	-1,199,889
STAFF_EXPENSES	1,955,210	29	2,253,692	18	1,192,062	763,147
ADMIN_EXPENSES	1,617,196	24	1,929,433	15	1,020,550	596,647
SELLI_EXPENSES	706,725	11	891,955	7	471,788	234,937
FINAN_EXPENSES	317,697	5	346,673	3	183,368	134,329
DEPRECIATION	166,439	2	172,717	1	91,357	75,082
TOTAL_EXPENSES	4,763,267	71	5,594,469	44	2,959,125	1,804,141
OTHER/INVEST_INCOME	2,694,430	40	2,766,285	22	1,463,192	1,231,238
NET_PROFIT	1,370,961	20	5,943,523	47	3,143,753	-1,772,792

[its_anandrjs]

Write the expression like

Create a variable

Let vSalesVal = //and store the sales values here

Let vBudgetVal = //and store the budget values here

Let vRevisedVal = //and store the revised values here

Dimension:- DESCRIPTION

ACTUAL:- SUM(ACTUAL)

Ratio:- (SUM(ACTUAL) /  $(vSalesVal ))*100

BUDGET:- SUM(BUDGET)

Ratio:- (SUM(BUDGET) /  $(vBudgetVal ))*100

Revised:- ( $(vRevisedVal ) / column(4) ) * 100

VARIENCE :- COLUMN(1) - COLUMN(5)

Hope this helps

[upaliwije]

Hi Anand

Thanks a lot You are helping me a lot. What you suggest is not very clear to me. Pls do the modification in the attached QV document, !

[its_anandrjs]

Another way of doing this with simple ways is.

Create a variable

Let vSalesVal  = Sum( {<DESCRIPTION={'SALES'}>} ACTUAL)

Let vBudgetVal =Sum( {<DESCRIPTION={'SALES'}>} BUDGET)

Let vRevisedVal =Sum( {<DESCRIPTION={'SALES'}>} Revised)

And

Dimension:- DESCRIPTION

ACTUAL:- SUM(ACTUAL)

Ratio:- (SUM(ACTUAL) /  $(vSalesVal ))*100

BUDGET:- SUM(BUDGET)

Ratio:- (SUM(BUDGET) /  $(vBudgetVal ))*100

Revised:- if(RowNo()=1,$(vSALES), $(vREVISED)*Column(4)/100 )

VARIENCE :- Column(1) -Column(5)