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