Sub total issue

[mohamed_ahid]

Hi every body,

I'm working on a pivot table like this :

Level	Canal	Cost this year	Cost last year	Weignt
RENAGEL	HP	4865	5641,65	46.22%
	MT	3506	5641,65	33.31%
	GP	2154	1927,8	20;46%
	Total	10525	1927,8	100%

for facts columns i used this formulas :

Cost this year : set analysis over Cost column using a varibles which returns this year number :

 

=sum({<Level={'level1'},Year={$(=year_n)}>}cost) 

Cost last year : same thing this the formula :

=sum({<Level={'level1'},Year={$(=year_n-1)}>}cost)

my question is what is the formula to calculate weight like in the  table ? 

exmple : 46.22%=(4865/10525)*100

THX

[#2]

Use below expression

num(sum(Cost)/sum(total Cost), '#,##0%')

[Sokkorn]

Hi,

Your [Weignt] column should have expression Num([Cost this year]/[Cost last year],'#,###.00%')

Regards,

Sokkorn

[mohamed_ahid]

thanx for your answer but this will not work, i tried it before .

because i need to calculat sum(total cost) for this year.

see formulas above.

[mohamed_ahid]

no it should have  expression cost this year/total cost this year

[#6]

Can u post ur qvw file?

it would be great.

[techvarun]

(Sales_{this year} - Sales_{last year}) / Sales_{last year} * 100

[techvarun]

100-(Cost last year/Cost last year%)

[techvarun]

both will work

[#10]

Hi,

Why don't to try like (Column(3)/Column(4))*100 in your expression weight?

here Column(3) is "Cost is this year" and Column(4) is "Cost last year".