topic Help with creating a pivot table in QlikView

Help with creating a pivot table

amit_shetty78 — Fri, 01 May 2009 22:37:01 GMT

Hi,

I need some help creating a pivot table.
The pivot table has 2 dimensions:
Business
Year

and 1 expression : count(businessid)

Then I modify the expression column from vertical to horizontal and get the table as shown below.

(I was unable to attach a file as the site would just kill my browser each time I click on the attach button)

Current output
Business	Year	a-2001	b-2002	c-2004	d-2005	e-2006	f-2007	g-2008
A		2	3	4	5	6	7	8	35
B		4	5	3	9	1	9	0	31


Desired output

Business	Year	a-2001	b-2002	c-2004	d-2005	e-2006	f-2007	g-2008
A		5.7%	8.6%	11.4%	14.3%	17.1%	20.0%	22.9%	100.0%
B		12.9%	16.1%	9.7%	29.0%	3.2%	29.0%	0.0%	100.0%

My query is how I can show the percentage instead of the count. I may be able to do it using set analysis but that would require me to create 7 expressions. I want to avoid this as it needs me to hard code the values and I want to keep it generic.

Is this possible in any way?

Appreciate any help on this.

Thanks.

Help with creating a pivot table

Oleg_Troyansky — Sat, 02 May 2009 00:10:45 GMT

Use the following:


count(businessid) / count ( TOTAL <Business> businessid)

The prefix TOTAL <Business> will let you calculate the total by Business.

Oleg

Help with creating a pivot table

Anonymous — Sat, 02 May 2009 00:13:58 GMT

Amit,

Hard to tell exactly without the application example, but it very well may be enough to use the expression
count(businessid)/count(all businessid)
or
count(businessid)/count(total businessid)
depending of what you mean by %. Don't forget to format the result correctly.
Also, I think that if you use set analysis, it still will be one expression.

Regards,
Michael