
1. Re: Sum of squares
Robin Kennedy Sep 11, 2012 6:29 AM (in response to steve.parsons)This should be fairly easy to accomplish by creating a calculated value (right click in Measures pane and select Create Calculated Field). Give your field a name, like 'Herfindahl Index' and then in the formula pane write
([A]/[TOTAL])^2+([B]/[TOTAL])^2+([C]/[TOTAL])^2
If you don't have a TOTAL measure, just replace it with ([A]+[B]+[C])
Does that do the trick?

2. Re: Sum of squares
steve.parsons Sep 11, 2012 7:03 AM (in response to Robin Kennedy)Hi Robin,
Thanks for your answer. However, I just realised that I posted my input data incorectly.
Your solution would have worked fine given the data set I gave, however, what I actually have is:
COMPANY, CATEGORY, VAL
OneCo, A, 1
OneCo, B, 5
OneCo, C, 4
TwoCo, A, 5
TwoCo, B, 0
TwoCo, C, 3
ThreeCo, A, 0
ThreeCo, B, 0
ThreeCo, C, 4
I've been working with customised grand total calculations at the moment, as my calculation goes across columns.

3. Re: Sum of squares
sirajmandayippurath Sep 11, 2012 7:29 AM (in response to steve.parsons)Steve,
Have you tried using the "WINDOW_" functions to address the sum of fields at the pane or table level?
I have created a sample with the data that you provide. Is this what you were looking for?
Cheers
Siraj

Calculation across Pane.twbx.zip 21.2 KB


4. Re: Sum of squares
steve.parsons Sep 11, 2012 8:56 AM (in response to sirajmandayippurath)Hi Siraj,
Thanks for the help. Your example gives me the indivual ratios for each A,B,C, but not the sum of their squares.
The output I want is one row per company as follows:
Company CalculatedValue
OneCo 0.42
TwoCo 0.5061728
ThreeCo 1
Thanks for the tip about the window_ functions. No joy so far, but I'll keep trying things out

5. Re: Sum of squares
sirajmandayippurath Sep 11, 2012 9:25 AM (in response to steve.parsons)Hi Steve,
I have added a second table calculation that allows you to get to that result.
You will also notice that in order to ensure that the Final Result is displayed on the final row, I have used the SIZE() and the INDEX() functions.
Hope this helps..
Cheers
Siraj

Calculation across Pane.twbx.zip 16.2 KB


6. Re: Sum of squares
steve.parsons Sep 12, 2012 1:27 AM (in response to sirajmandayippurath)Very helpful, thanks.
Ideally, I don't want to show the extra columns and rows. Do I just have to 'hide' them manually, or is it possible to remove them properly ?
If I take category off the rows, and put it in the level of detail box, I get overlapping text. I've not found a way to get the correct results using things like 'IF FIRST()=0' or IF(INDEX()=SIZE()'.