topic Re: Need help in Pivot table in QlikView

Need help in Pivot table

Anonymous — Fri, 30 Sep 2016 09:53:17 GMT

Hi Expert

I have pivot table as below and need formula to get values for exp5

Exp1,Exp2,Exp3 are calcualted expression with more formula ex:sum({<Type={'Revenue'},MthOrder={">=$(=vMaxMth-2)<=$(=vMaxMth)"},Mth=,FiscalYear=>}ValueCol1)

Dim1	Dim2	Exp1	Exp2	Exp3	Exp4	Exp5
					(Exp1+Exp2)-Exp3	(Total DIM1 Values)


1U00	Ctry1	30	25	15	40	50(40+10)
	Ctry2	35	75	100	10	50(40+10)


1P00	Ctry1	35	27	38	24	69(24+45)
	Ctry2	23	44	22	45	69(24+45)

Re: Need help in Pivot table

Fri, 30 Sep 2016 10:05:36 GMT

explain the exp5= ?

Re: Need help in Pivot table

avinashelite — Fri, 30 Sep 2016 10:09:19 GMT

What is the logic to calculate exp5??

Re: Need help in Pivot table

mohamed_ahid — Fri, 30 Sep 2016 10:17:53 GMT

HI,

aggr(sum(exp4) , Dim1)

Sum(<Dim1>aggr(sum(exp4) , Dim1))

Re: Need help in Pivot table

antoniotiman — Fri, 30 Sep 2016 10:27:46 GMT

Try

RangeSum(Top(Column(4),1,NoOfRows()))

Re: Need help in Pivot table

Anonymous — Fri, 30 Sep 2016 10:27:52 GMT

thanks Mohamed i got the answer by your formula.

Re: Need help in Pivot table

Anonymous — Fri, 30 Sep 2016 10:33:32 GMT

Thanks Antonio i got answer by your formula , can you please explain formula. I could not understand

Re: Need help in Pivot table

antoniotiman — Fri, 30 Sep 2016 10:38:30 GMT

Sum values of Column 4 from First Row to Last row of Dimension 1

You can change Column(4) to Label Name of Column or repeat same Expression

See RangeSum() function and Top() function in QV help

Regards,

Antonio