Set Analysis Field1 - Field2

[#1]

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.

[johnw]

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)).

[swuehl]

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