topic Re: Sub total issue in QlikView

Sub total issue

mohamed_ahid — Tue, 16 Jul 2013 10:30:09 GMT

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%
RENAGEL	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

Re: Sub total issue

Tue, 16 Jul 2013 10:39:44 GMT

Use below expression

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

Re: Sub total issue

Sokkorn — Tue, 16 Jul 2013 10:46:58 GMT

Hi,

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

Regards,

Sokkorn

Re: Sub total issue

mohamed_ahid — Tue, 16 Jul 2013 10:49:26 GMT

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.

Re: Sub total issue

mohamed_ahid — Tue, 16 Jul 2013 10:51:02 GMT

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

Re: Sub total issue

Tue, 16 Jul 2013 11:21:26 GMT

Can u post ur qvw file?

it would be great.

Re: Sub total issue

techvarun — Tue, 16 Jul 2013 12:23:32 GMT

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

Re: Sub total issue

techvarun — Tue, 16 Jul 2013 12:32:59 GMT

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

Re: Sub total issue

techvarun — Tue, 16 Jul 2013 12:33:20 GMT

both will work

Re: Sub total issue

Tue, 16 Jul 2013 12:33:23 GMT

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".

Re: Sub total issue

stevelord — Tue, 16 Jul 2013 14:30:25 GMT

I see assumed answered, but had one more neat trick to contribute.

Just make another column with the same expression, then check the 'relative' box and set totals mode to 'expression totals' for that expression. (These options are on the expressions tab.). That's if you want one column for the counts and one for the percentages. You could just check the relative box on the expression you have to make it go to percentages.