| Author |
Topic |
|
pmak
Starting Member
41 Posts |
 Posted - 2006-03-25 : 19:40:41
|
| I need help to write an expression to compute the population percentage for two vertical (row) groupings and one horizoontal (column) grouping in a Matrix report...1st row grouping is the State2nd row grouping is the CityColumn grouping is the YearThe matrix report consists of the "State", "City" and Year". The population is showing in the "Year" column grouping and I create the second column next to the population and named it "Percentage". Also I added the population subtotal for the State and City. I need to write an expression to compute the population percentage, State's population percentage of the subtotal of the State's population and City's population percentage of the subtotal of the city's population of that State, and all the population percentage is computed based on the column grouping "Year". Thanks. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-03-25 : 20:00:16
|
Please post your table DDL, some sample data and expected result
KHChoice is an illusion, created between those with power, and those without.Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant |
 |
|
|
pmak
Starting Member
41 Posts |
Posted - 2006-03-26 : 17:44:43
|
| It is only the sample data, the first column showing the name of the Canadian Province and the second column showing the cities of that province and the state of the text box can be toggled on and off by the province. Percentage should be computed on the row grouping value or row value and also based on the "All Fiscal Year" or a "Fiscal Year" total on that column. <-------Collapsed or expanded-------> Fiscal Year 2002 2003 Population % Population % AB Calgary 850,000 0.89 % 1,020,000 1.07 % Edmonton 750,000 0.78 % 780,000 0.81 % Fort McMurray 35,500 0.04 % 42,400 0.04 % Red Deer 20,000 0.02 % 25,700 0.03 % Provincial Total1,655,500 1.73 % 1,868,100 1.95 %BC Burnaby 750,000 0.78 % 904,000 0.94 % Kelowna 250,000 0.26 % 300,000 0.31 % Vancouver 3,000,000 3.13 % 3,600,000 3.76 % Victoria 500,000 0.52 % 608,000 0.63 % Whistler 35,000 0.04 % 42,080 0.04 % Provincial Total4,535,000 4.74 % 5,454,080 5.70 %MB Churchill 250,000 0.26 % 300,000 0.31 % Winnipeg 150,000 0.16 % 180,000 0.19 % Provincial Total400,000 0.42 % 480,000 0.50 %NB Fredericton 865,250 0.90 % 1,038,300 1.08 % Provincial Total865,250 0.90 % 1,038,300 1.08 %Total 16,982,200 17,455,020 It is the expression I wrote early for the percentage and it works for "All Fiscal Year" computation but not individual fiscal year.=IIF(InScope("matrix1_Province"), IIF(InScope("matrix1_AllYear"),Sum(Fields!Population.Value)/Sum(Fields!Population.Value,"matrix1_AllYear"),Sum(Fields!Population.Value)/Stdevp(Fields!Population.Value,"matrix1_FiscalYear")),"") |
|
|
|
|
|
|
Matrix Percentage Expression