Visokio website     Downloads     Video tutorials     KnowledgeBase  
Formulae: Subset_sum formula error? - Visokio Forums
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,


  • 4 Comments
  •     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.
    image
    Attachments
    testFormula.JPG 60K
  •     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
    Attachments
    Subset2 Ex.iok 10K

Welcome!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership

Tagged