Formulae: Subset_sum formula error?

PSliz · April 2, 2014 5:53AM

Hi,

Please could someone help with the following formula;

(subset_sum([% Popn], subset2([Reach Cat],[ctyName], [Reach Cat], 'Warwickshire','=','='))/subset_sum([% Popn], subset([Reach Cat])))*100

In words: first I am trying to sum the resident population ([%Popn]) for each of the Reach categories (10 categories, [Reach Cat]) where the county ([ctyName]) is Warwickshire. Then I want to divide this number by the sum of the resident population in each of the Reach categories across the whole table.

I keep getting the error "Cannot use field value references. This formula is evaluated in the context of multiple records, without any specific individual records." The problem seems to be with the second "Reach_Cat" in subset2.

I am trying to apply this formula in the measures field of a bar/line chart. The chart will have Reach_cat categories along the x-axis.

%Popn is a decimal number field.
Reach_Cat and ctyName are both category fields.

Please could someone tell me;
1. Exactly what this error means, as I have seen it before but don't know exactly what problem it is indicating.
2. How to fix this formula error.

Thanks in advance for any help,

tjbate · April 2, 2014 6:14AM

Lisette - What happens if you add this new Formula Field to your data set in DataManager using an Field Organiser block? Does the new field/column calculate and show the values correctly?

paola · April 2, 2014 6:16AM

It is difficult to test the formula without the dataset, so please try to add a formula field with this formula and, if it works, use the field as measure for the Bar/Line view.
You can also use Formula window diagnostic tools, such as Test and Simplify. Slide the right border towards the centre to get more info about formula elements.

Bart · April 2, 2014 6:20AM

You can't put subset_sum([% Popn], subset2([Reach Cat],[ctyName], [Reach Cat], 'Warwickshire','=','='))/subset_sum([% Popn], subset([Reach Cat])))*100 into bar view formula as you need to specify [Reach Cat], why won't you just remove [reach Cat] from the formula?? And then just split bar by reach Cat. You should get your results by doing so

daniel · April 2, 2014 1:24PM

I can't get the bar/line graph view to evaluate it either, omitting reach does work though or alternatively creating the same formula as a new field and putting that in the bar chart also works.

It's possibly because the bar/line view isn't reading the data as a whole table but doing it line by line. Given that there's multiple lines with the same reach value, it might be that causing the error.

See attached

Welcome!

Categories

Ideas Parade

Tagged