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:

           

          {<Field1={"<$(=Field2+29)"}>}

           

          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.

             

            Regards,

            Stefan