Spotfire Heiarchy Percent Aggregation similar to excel pivot group by

0
votes

First let me say that I am extremely new to Spotfire(1 Day) Anyways I have some data that looks like the following:

Region Location Animals Cats % of Cats West A 10 7 .7 East A 15 10 .66 East B 10 9 .9 West B 5 1 .2 East A 10 6 .6 West B 15 5 .33

When I make a new visualization I want everything to be grouped by the Region level and then drill down to the location whats happening is it is summing the percentage instead of calculated the percentage on the total for regions. Example:

Region % of Cats West 123% East 216%

Instead of this happening i was expecting it to look more like a pivot table in excel where it can auto calculate the different percentage levels based on the the levels of hierarchy.

The correct output should look like this:71.43 4.33

Region % of Cats West 71.43% East 43.33%

Where its taking the sum of all calls converted to sales and dividing it by the sum of all call to determine the converted percentage by region. I've tried to change every aggregation method. Rewrote the calculated column etc. ANd all it ends up doing is summing up all the percentages and making it total over 100%. Hopefully I explained this properly. Thanks for the help

1
pivottibcospotfire
have you explored the cross table visualization yet? – jleviaguirre
Yes, but when I add a calculated column like the division of 2 columns to get a percentage, it still sums the percentage instead of doing the calculation on the total and giving me the correct percentage. For example in an excel pivot table if you create a actuated column it automatically recalculates the percentage at every grouping no matter how you slice and dice the data. – TPLEE

1 Answers

1
votes

You need to use a bit of custom expression here, instead of trying with "% of cats" (it simply doesn't hold enough information), go to "edit expression" (at the bottom of the form where you tried adding aggregations) and enter:

Sum([Cats]) / Sum([Animals])