topic Re: Re: Pivot Table calculations in QlikView

Pivot Table calculations

Tue, 06 May 2014 15:49:07 GMT

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

Re: Pivot Table calculations

Anonymous — Tue, 06 May 2014 15:52:52 GMT

Depending on what is the base of the %, it could be

Sum([Number Of Fatalities])/Sum(total [Number Of Fatalities])

Sum([Number Of Fatalities])/Sum(total<feield1, field2,...> [Number Of Fatalities])

Re: Pivot Table calculations

Tue, 06 May 2014 16:04:56 GMT

Thanks Michael. I think this is the solution, but I need to know how "total" works.

Re: Pivot Table calculations

Tue, 06 May 2014 16:07:13 GMT

P.S. It did work. Although I am not completely sure how.

Re: Pivot Table calculations

Tue, 06 May 2014 16:08:21 GMT

I take that back. When I did it, it used the total number of fatalities for all systems for all years, When I expand it out to the years for each functional system, I need it to do the percentage for the functional system only.

Re: Re: Pivot Table calculations

Anonymous — Tue, 06 May 2014 16:42:37 GMT

See attached with four versions of % calculations. Choose what you need, or create your own by modifying/combining...

Re: Pivot Table calculations

Tue, 06 May 2014 16:59:56 GMT

Michael,

When people have sent me attachments, they do not appear in the view I have of the discussion. I found a different view by accident once where I could get to the attachements. Any idea how I get there so I can view your attachment? Thanks.

Re: Re: Pivot Table calculations

Anonymous — Tue, 06 May 2014 17:13:57 GMT

My bad - I didn't attach anything...

Re: Pivot Table calculations

Tue, 06 May 2014 17:15:39 GMT

Whew! I feel much better now! Thanks!

Re: Pivot Table calculations

Tue, 06 May 2014 17:17:30 GMT

Any way to send it as a doc instead of a QVW? I only have the personal edition of Qlikview and have a limited number of files I can open as a QVW.

Re: Pivot Table calculations

Anonymous — Tue, 06 May 2014 18:49:19 GMT

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.

Re: Pivot Table calculations

Thu, 08 May 2014 15:14:06 GMT

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!