2 Replies Latest reply: Dec 30, 2011 4:31 PM by Stefan Wühl RSS

    Set Analysis Field1 - Field2

    Igor Alcantara

      I want to include in my Set Analysis a statement like this:


      Field1 - Field2 < 29


      I tried


      Field1 - Field2 = {"<29"}


      And also


      $(=Field1 - Field2) = {"<29"}


      And other variations of these but none of them worked.


      Does anyone know how can I do this?


      By the way, precalculate this in the load script is not an option.

        • Re: Set Analysis Field1 - Field2
          John Witherspoon

          You can't have anything other than a single field on the left side of the =.


          I don't think you can do what you want with set analysis unless either Field1 or Field2 has just a single possible value.  A set is evaluated once for the entire chart, so if both fields have multiple values, you can't use set analysis for the chart.  If Field2 has a single value, I believe you could write it like this, and something similar if Field1 has a single value:




          If Field1 and Field2 can have multiple values, you'll probably need to do it with an if(), such as sum(if(Field1-Field2<29,Value)).

          • Re: Set Analysis Field1 - Field2
            Stefan Wühl

            I also assume that Field1 and Field2 can have multiple values, furthermore, I assume that these values are not unique. So you could have combinations for Field1 and Field2 like

            Field1 Field2

            30      10

            40      10

            30       1


            Only the first record will match your requirement.


            I also think that you can't use a set expression to select on Field1 or Field2, at least I can't think of a correct syntax.


            But if you have a field with unique records, like a record ID (e.g. using recno() in the load),  I think something like this should work:


            =sum({<ID={"=(Field1-Field2)<29"}>} Value)


            Please see attached sample.