topic Calculation in Pivot Table in QlikView

Calculation in Pivot Table

upaliwije — Mon, 14 Jul 2014 15:14:50 GMT

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

its_anandrjs — Mon, 14 Jul 2014 15:34:10 GMT

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 — Mon, 14 Jul 2014 15:44:56 GMT

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 — Mon, 14 Jul 2014 16:34:25 GMT

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)