Good afternoon,
I have a structure which has two breaks, then I add the percentual value for a specific value field. What I would like to do, is to sort not just based on the value field - which it does via sort function - but also sort the members of the second break based on the footer's percentual value.
Fact is, I don't really need it to sort based on the value, but rather on its sub-total only. Sorting the value within each break is convenient, but not critical.
How can I achieve this?
can you plz explain with screenshot
Yes I can, I apologize for not having done so before.
Attached is a screenshot, I had to cut most of the columns becuase it is sensitive user information. So basically the first column (highlighted 65) is the first break, within the first break, a second break is created for the second column (for that particular case, of the value 65, the 2nd order beraks correspond to 201.52 and 201.9). The last column is actually a percentage function of the former column. As you can see, for the 2nd break footers were inserted, which include the sub-total of the percentage function in terms of the 1st break. Now when I sort the last column (percentual), it solely organizes the contents of the 2nd break, take the case of the 65 | 201.52, it sorts properly the 2nd break: 82.56% then 17.44%, and 83.06% then 16.92%. However, the 9.98% comes before the 90.02%, that is, it is only sorting within the 2nd break level. In reality, 90.02% > 9.98% so it should place 201.9 before 201.52. Sounds a bit confusing, but the screenie should make it clear.
Hi,
I could only think of a workaround that might be helpful.
1. Create a formula using rank : vRank =rank(Measure;Dimension)
2. Insert vRank in the first column
3. Sort and apply Break
4. Hide the vRank column
Here is the another scenario posted in SCN
https://archive.sap.com/discussions/thread/3220060
Thanks,
Jothi
I tried it, both yours and the version on the link, can't seem to get it working.
I even aded a cumulative sum column, and applied the ranking to it with two breaks, nothing seems to work.
Solved the issue by: computing the 2nd-order break total percentage on each cell. Removed all breaks. Sorted by the mentioned field. Then re-added all the breaks.
Glad it worked!!! :)
Thanks,
Jothi