How to show the weight (share) in a pivot table?

[#1]

I am trying to solve the following problem, but I just can't figure it out for the moment. I have the sales figures by article. The articles are grouped in classes and the classes in families. I want to present, into a pivot table, the weight (share) of each class into the family, of each article into the class. So my pivot table should look like this:

Family Class Article SalesAmount SalesShare

A A1 A11 1000 33%

A12 2000 67%

A2 A21 1000 12.5%

A22 3000 37.5%

A23 4000 50%

B 20000 100%

When I am collapsing the articles, it should look like this:

Family Class Article SalesAmount SalesShare

A A1 3000 27%

A2 8000 73%

B 20000 100%

Finally, collapsing it should like this:

Family Class Article SalesAmount SalesShare

A 11000 100%

B 20000 100%

[johnw · Accepted solution]

This appears to be working:

sum(SalesAmount)/pick(dimensionality()
,sum(total SalesAmount)
,aggr(nodistinct sum(SalesAmount),Family)
,aggr(nodistinct sum(SalesAmount),Family,Class))

[#3]

This is working just great! Thanks a lot, man! I owe you!

[#4]

HI, I have tried in qlikview application, but when i click the (-) symbol the last column will come to 35% not 100%

I have uploaded the document. can you tell me what is the problem

Thanks,

Raghu.

[johnw]

Well, it looks like I misread the question. I thought it was supposed to always be the percentage of the next level up, so that at the top level, we should see 35% and 65%. Reading again, that is obviously incorrect. Try this instead:

sum(SalesAmount)/if(dimensionality()=3
,aggr(nodistinct sum(SalesAmount),Family,Class)
,aggr(nodistinct sum(SalesAmount),Family))

[#6]

I am working on the pivot table and I came across this post. This solution works fine for my pivot table with only one problem. As my pivot table comprises of two cycle group dimension it crashes after doing an extensive calculation on the lowest level. Is there any way we can performance tune this below expression or write it in some other way?

Sum (AMOUNT) / pick(dimensionality()
,sum(TOTAL AMOUNT)
,aggr(nodistinct sum(AMOUNT), [Cycle_Group_1])
,aggr(nodistinct sum(AMOUNT), [Cycle_Group_1],[Cycle_Group_2])
,aggr(nodistinct sum(AMOUNT), [Cycle_Group_1],[Cycle_Group_2],Field_Name_1))

Where Cycle_Group_1 goes '5' level down and Cycle_Group_2 goes '6' level down.

[#7]

No performance tunning was required. Problem was coming from datamodel it self. Apologies for the confusion.