Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a pivot table and it has a category of "Functional System". For each Functional System (a type of highway) it gives me the total number fatalities for a 30 year period. When I expand the Functional System, it then breaks out the the number of fatalities by year. I am actually looking to have it show the percentage of fatalities of the total as opposed to the total number. I have tried Set Analysis, but this does not seem to work. I want it to show the percentage when the fields are collapsed or expanded. Currently I have the expression as: Sum([Number Of Fatalities]) .
I can describe what is there.
Script for the test data, three years, three types:
load * inline [
Year, Type, Number
2000, A, 10
2000, B, 8
2000, C, 7
2001, A, 5
2001, B, 3
2001, C, 1
2002, A, 13
2002, B, 9
2002, C, 11];
Front end - five pivot tables, all with dimensions Type and Year. Expressions are different in each table
1. sum(Number)
2. sum(Number)/sum(total Number)
3. sum(Number)/sum(total<Type> Number)
4. sum(Number)/sum(total<Year> Number)
5. sum(Number)/ if(Dimensionality()<2, sum(total Number), sum(total<Year> Number))
See what makes more sense for you.
Michael,
#5 works...sort of. When I expand to the second dimension it gives 100% for all of the years. So, I changed the Dimension for when I expand it out from Year to Functional System and it worked flawlessly! Thanks for your help!