topic Re: Set Analysis Field1 - Field2 in QlikView

Set Analysis Field1 - Field2

Fri, 30 Dec 2011 17:21:06 GMT

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

johnw — Fri, 30 Dec 2011 17:37:43 GMT

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

swuehl — Fri, 30 Dec 2011 21:31:37 GMT

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