topic values/Total in pivot table in QlikView

values/Total in pivot table

Fri, 03 Jul 2015 11:09:39 GMT

Hi Experts,

i have the following tables,

BRANCH:

LOAD DISTINCT

BRNO,

SALESREPORTS,

Year(Date) as AGE

FROM [BRANCH.qvd](qvd);

Sales:

LOAD BRNO,

DATE,

Amount

FROM

[SALES.qvd]

(qvd);

from this, i have produced the following pivot table,

Now, i need to add another expression as Share.

formula is Share = Amount/Total (i.e., for age 2010, the share is 25896/185429 = 13.96%)

How can i do this in a pivot table.

plsss help.

Re: values/Total in pivot table

alexandros17 — Fri, 03 Jul 2015 11:12:07 GMT

Try with

Sum(amount)
/
Sum(TOTAL amount)

let me know

Re: values/Total in pivot table

swuehl — Fri, 03 Jul 2015 11:13:38 GMT

=Sum(Amount) / Sum(TOTAL Amount)

You can format the number on number tab as percentage.

Re: values/Total in pivot table

sunny_talwar — Fri, 03 Jul 2015 11:19:32 GMT

Alessandro Saccone solution should work, just need to make sure that Amount has a upper case A because QV is case sensitive.

Sum(Amount)
/
Sum(TOTAL Amount)

HTH

Best,

Sunny

Re: values/Total in pivot table

Fri, 03 Jul 2015 11:20:58 GMT

Hi,

i m trying this,

SUM({<MonthYear=, Date={"$(vCurrYr)"}>}Amount)/SUM(TOTAL{<MonthYear=, Date={"$(vCurrYr)"}>}Amount)

i m not getting correct values.

Pls help.

is the expression correct?

Re: values/Total in pivot table

Fri, 03 Jul 2015 11:23:51 GMT

Hi,

i m trying this,

SUM({<MonthYear=, Date={"$(vCurrYr)"}>}Amount)/SUM(TOTAL{<MonthYear=, Date={"$(vCurrYr)"}>}Amount)

i m not getting correct values.

Pls help.

is the expression correct?

Re: values/Total in pivot table

sunny_talwar — Fri, 03 Jul 2015 11:25:47 GMT

I don't see any problem with your expression. Can you share may be a sample or a screenshot of what you getting?

Re: values/Total in pivot table

swuehl — Fri, 03 Jul 2015 11:32:22 GMT

Are you using more than one dimension, which you are not showing in above screenshot?

Then you may need to use a field list with your TOTAL qualifier, something like

=Sum(Amount) / Sum(TOTAL<OtherDimField> Amount)

Re: values/Total in pivot table

Fri, 03 Jul 2015 12:16:11 GMT

Hi,

i m getting this

but the value should be

pls help

Re: values/Total in pivot table

Fri, 03 Jul 2015 12:19:54 GMT

Hi Swuehl,

Tried this,

SUM({<MonthYear=, Date={"$(vCurrYr)"}>}Amount)/SUM(TOTAL<{<MonthYear=, Date={"$(vCurrYr)"}>}Amount>)

but getting NULL values.

is my expression wrong?

pls help

Re: values/Total in pivot table

swuehl — Fri, 03 Jul 2015 12:27:19 GMT

yes, I suggested a field list to the TOTAL qualifier, not a set expression.

I assume that your issue is caused by the way your two tables are linked,

Your dimension is age, but you are linking the tables by Branch. Hence depending on the relation of age to Branch, I could think of conditions where your Sales values won't sum up to 100%.

Could you upload small sample QVW to check if this is the case?

Re: values/Total in pivot table

sunny_talwar — Fri, 03 Jul 2015 13:06:12 GMT

You can try this:

Sum(Amount)/Sum(Aggr(Sum(Value), AGE))

Re: values/Total in pivot table

Fri, 03 Jul 2015 13:31:41 GMT

found the issue.

missed a parameter on expression.

now fixed it.

got the values.

thanku all