topic How to use an expression value in set analysis in QlikView

How to use an expression value in set analysis

Fri, 12 Oct 2012 12:37:42 GMT

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

Re: How to use an expression value in set analysis

nilesh_gangurde — Fri, 12 Oct 2012 13:05:19 GMT

please find the attachement with the same results.

Regards,

Nilesh Gangurde

Re: How to use an expression value in set analysis

jonathandienst — Fri, 12 Oct 2012 13:06:53 GMT

Sami

Try the following calculated dimension for your chart:

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

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

And Sum(Sales) as the expression

Jonathan

Re: How to use an expression value in set analysis

jonathandienst — Fri, 12 Oct 2012 13:08:15 GMT

Or use Customer as dimension and

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

as expression (and ensure that suppress zeros is checked.

Regards

Jonathan

Re: How to use an expression value in set analysis

Fri, 12 Oct 2012 14:40:40 GMT

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

Re: How to use an expression value in set analysis

Fri, 12 Oct 2012 14:49:24 GMT

Jonathan,

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

Chart:

Sales_order Sales

837282 1000

Best regards

Sami

Re: How to use an expression value in set analysis

jonathandienst — Fri, 12 Oct 2012 14:57:07 GMT

Sorry, my mistake

Use Sales_Order as dimension

Jonathan

Re: How to use an expression value in set analysis

Fri, 12 Oct 2012 15:27:59 GMT

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

Re: How to use an expression value in set analysis

Fri, 12 Oct 2012 15:38:01 GMT

qvw file attached.