How to use an expression value in set analysis

[#1]

Hi

Is it possible to create a set analysis expression that uses value from a chart row?

I have two data sources Table1 and Table2. Table1 contains Customer and status information. Table2 contains sales orders and the link information that contains customer numbers.

Table1:

Customer            status

111222                A

111333                B

111444                A

Table2:

Sales_order    Link          Sales         

837282          111222      1000

364827          111333      2000

384634          removed      500

I need a chart that only shows the sales from customers with status A. The result would look like this.

Chart:

Sales_order     Sales

837282            1000

The qvw-file attached.

Thanks in advance,

Sami

[nilesh_gangurde]

please find the attachement with the same results.

Regards,

Nilesh Gangurde

[jonathandienst]

Sami

Try the following calculated dimension for your chart:

Aggr(Only({<Status = {'A'}>} Customer), Customer)

or

Aggr(Only(If(Status = 'A', Customer)), Customer)

And Sum(Sales) as the expression

Jonathan

[jonathandienst]

Or use Customer as dimension and

Sum({<Status = {'A}>} Sales)

as expression (and ensure that suppress zeros is checked.

Regards

Jonathan

[#5]

Nilesh,

In your solution "Link" has been renamed to "Customer", but it is causing another problems and can not be done. That makes it difficult.

Best Regards,

Sami

[#6]

Jonathan,

Using Customer as dimension won't work as I need sales by sales order.

Chart:

Sales_order     Sales

837282            1000

Best regards

Sami

[jonathandienst]

Sorry, my mistake

Use Sales_Order as dimension

Jonathan

[#8]

Yeah, but it won't work as there is no relation between Table1 and Table2.

These two tables are data islands.

But, thanks for fast answers.

-Sami

[#9]

qvw file attached.