Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)).
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