topic Re: % of Row Total Expression in QlikView

% of Row Total Expression

muniyandi — Thu, 27 Mar 2014 08:04:40 GMT

Hi ,

In pivot table more then one dimension and single expression how to compare the current row value to Row Total value.

Kindly verify my excel attachment file.

Thanks.

Re: % of Row Total Expression

tresB — Thu, 27 Mar 2014 08:10:35 GMT

Use TOTAL keyword to calculate %, like:

=Sum(Sales)/Sum(TOTAL Sales)

Edit: Corrected order

Re: % of Row Total Expression

Thu, 27 Mar 2014 08:35:03 GMT

Guess it would be the other way around, but Total should solve it yes;

Quarters etc as Dim and then;
Sum(Sales)/Sum(Total Sales) and set numbers to show as percentage.

Re: % of Row Total Expression

muniyandi — Thu, 27 Mar 2014 09:16:42 GMT

Hi,

sum(sales)/Sum(total sales) is Column Total.i want Row totalwise comparison.

Re: % of Row Total Expression

tresB — Thu, 27 Mar 2014 09:21:28 GMT

Not sure, may be you need to change the total mode in the expression tab. Could you post a sample qvw?

Re: % of Row Total Expression

its_anandrjs — Thu, 27 Mar 2014 09:34:35 GMT

Use like this

Column(1) + Column(2) for Total

for direct % Column(1) / (Column(1) + Column(2))

for Indirect % Column(2) / (Column(1) + Column(2))

and in number properties change it into number %

Hope this helps

Thanks & Regards

Re: % of Row Total Expression

muniyandi — Thu, 27 Mar 2014 12:05:33 GMT

Hi ,

Here i attached sample file. require result to see the excel.

try with excel pivot table and to be select on sum of amount then values in % of Row total.

kindly verify the attachment excel file. sheet 3 are expected result.

Thanks.

Re: % of Row Total Expression

Thu, 27 Mar 2014 13:55:51 GMT

Hey Mathew, try this;

Instead of including your Type as a dimension, just include your "New group" and add 2 expressions with set analysis;

Expression 1 : sum({$<Type= {Direct}>}Amount)/sum(Amount)
Expression 2: sum({$<Type= {Indirect}>}Amount)/sum(Amount)

Edit in Numbers as show percentage, and add Direct/Indirect labels to them however you want to do it

Re: Re: % of Row Total Expression

jerem1234 — Thu, 27 Mar 2014 14:24:50 GMT

Please find attached. Used this expression:

sum(Amount)/sum(TOTAL <Quarter>Amount)

You needed to add the <Quarter> to the total from the previous replies.

Hope this helps!

Re: % of Row Total Expression

Thu, 27 Mar 2014 14:36:58 GMT

This would work for the Quarterly dimension, but not for the Monthly dimension (there's a cyclic group in there).

Re: % of Row Total Expression

jerem1234 — Thu, 27 Mar 2014 14:46:32 GMT

Use this formula instead:

sum(Amount)/sum(TOTAL <$(=GetCurrentField("New Group"))>Amount)

Hope this helps!

Re: % of Row Total Expression

muniyandi — Fri, 28 Mar 2014 05:24:37 GMT

hi,

Dim1	Dim2
	A	B	C	D	E	F	Row Total
Jan-10	1000	2000	1500	1400	1600	1000	8500
Result	11.76%	23.53%	17.65%	16.47%	18.82%	11.76%	100.00%

friends,

i need calculate to Current row Total value with current cell value comparison .

1) First Get row total value 8500 ; ( how to get 8500)

2) current row Jan-10 and cell is A Now (1000/8500)*100 = 11.76

Just refer my attachment excel pivot table.

Normally we are only get on column total only. how to get row total and dynamically change the dimension means how to handle.

//Sum(amount) / Sum(Total amount) is Column Total . (That is get the total amount of amount Column)

Thanks,

Muni.