topic Re: Getting a column total in a pivot table in QlikView

Getting a column total in a pivot table

Tue, 28 Aug 2012 16:19:37 GMT

Kind	Inv Name	Created	Sold	% Sold
Grant	part1	5	2	40%
Grant	part2	2	1	50%
Total		7	3	43%

I know I've seen this before but I'm struggling to find it. Is it possible to get the column total and use it in an expression to set a background color.

I've done this in the past but I had to recreate the expression. This is a very simple example, my actual example is much more difficult.

What I want to do is use the number 7 (total for created) in the Background Color for % Sold. So, for example, I'll color 40% red since it's under the %Sold Total of 43%.

Can I somehow just get the column(1) total somehow?

Re: Getting a column total in a pivot table

shree909 — Tue, 28 Aug 2012 16:32:10 GMT

Hi on the % told KPI go to the background and write an if statement

if( %sold <'40 %' red(), green())

hope this helps .if not attach an qvw file

Re: Getting a column total in a pivot table

bgerchikov — Tue, 28 Aug 2012 16:39:46 GMT

Shree,

your expression will change backgroung for the entire entire column. To have background color for the total line you should add dimensionality:

if(dimensionality()=0 and %sold <'40 %' red(), green())

Re: Getting a column total in a pivot table

whiteline — Tue, 28 Aug 2012 16:40:14 GMT

Expand the %Sold expression in the chart properties.

For Backgroud Color use something like this

=if([%sold expression] < [%sold expression with total], lightred(), lightgreen())

[%sold expression] - is your expression for %Sold

[%sold expression with total] - is your expression for %Sold with total modifier.

for example: Sum(value), Sum(total value) - with total modifier.