Set Analysis: Selection based on values from disconnected field.

[#1]

I have a disconnected table which I'm trying to use to define a set, for example

Sum({<Value=DisconnectedValue>} Amount)

As specified in help:

There are several ways to define the selection: A simple case is a selection based on the selected values of another field, e.g. <OrderDate = DeliveryDate>. This modifier will take the selected values from DeliveryDate and apply those as a selection on OrderDate. If there are many distinct values - more than a couple of hundred - then this operation is CPU intense and should be avoided.

But I can't get it to work. Anyone have any pointers? Thanks.

[#2]

I haven't used that Set Analysis functionality yet (although I have seen it in the guide).

I just put together a simple example and it seems to work fine. What do your values look like in that field? Are they text or numbers? I was able to put quotes around DisconnectedValue and get it to work, if you have text values, that may be worth a try.

[#3]

I have numbers.

Where you able to filter on one or multiple values from your disconnected table?

[#4]

Yes, it seemed to work for one or multiple. When I select none, the chart is empty.

Here's a sample file that uses Inlines for the data.

There does seem to be some funny stuff going on though. It doesn't seem to respect the selection (I didn't include the dollar sign). When I add the dollar sign, nothing works.

[Oleg_Troyansky]

this syntax (Field1 = Field2) only works when anything is selected within Field2.

In version 9, you can use a new feature "use possible values" - Filed1 = P(Field2)

In version 8.5, you can use function concat() as a work around. See attached.

cheers!

[#6]

Hi Oleg,

I did make a transformation to your published content to better highlight my dilemma in what regards to this issue:

The situation is as follows:

We have 2 tables (1 and 2) where Table2 is an island table

Table1 shows account sales and the entire table is contrained by login.

Table2 shows national data which are necessary to build some comparisons.

The Field1 (Table1) and Field2 (Table2) have exactly the same values, meaning that the records in Field1 match enterily the records in Field2.

I wonder how can they be linked on the expression in order that the straight table in the example calculates accuratlly both the sum(AMOUNT1) and sum(AMOUNT2) for each of the records listed when Field1 is used as a Dimension.

Appreciate all your help

Cheers

Joao

[#7]

One of the solutions is to use aggr:

sum(if (FIELD1=FIELD2, aggr(sum({$<FIELD2 = {$(vConcat2)} >} AMOUNT2), FIELD2)))

see attached qvw. Hope that's what you want.

[#8]

Hi Lingxiang,

Thank you so much for this solution. Fits perfectly!!!!!

Regards

Joao Morais