topic Re: Set Analysis question in QlikView

Set Analysis question

Tue, 14 Jun 2011 12:53:16 GMT

Hi!

I need help with a set analysis question. When I am selecting Supplier 1 in below example I want qlikview (in a graph) to summarize Sales for all categories that are related to Supplier 1 (see underlined Sales). Hence I want a column summarizing to 41 when I have selected Supplier 1.

What is the expression for that? Prefferably in a SET Analysis expression.

Supplier	Category	Sales
1	Shoes	10
1	T-Shirts	20
2	Balls	25
2	Shoes	5
2	Tennis	15
3	Tennis	3
3	T-Shirts	6

Best Regards

Christer Ekblad

Re: Set Analysis question

Miguel_Angel_Baeyens — Tue, 14 Jun 2011 13:03:30 GMT

Hello Christer,

That's one of the purposes P() element function in set analysis is for. If you create a new chart, straight or pivot table, with category as dimension and the following as expression you should get as expected:

Sum({< Category = P({< Supplier = {1} >}) >} Sales)

That reads something like "return the sum of all values in Sales where Category values corresponds to those Supplier 1 has". Kind of.

Hope that helps

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Set Analysis question

Tue, 14 Jun 2011 13:48:03 GMT

Hi thanks for reply.

However, what if I select supplier 2, then above does not work. How shouold I modify the above expression so that it is more dynamic and work for all selections i supplier?

(There might also be situations where I select several suppliers)

Best Regards

Christer Ekblad

Re: Set Analysis question

Miguel_Angel_Baeyens — Tue, 14 Jun 2011 14:06:35 GMT

Hello Christer,

Then you may use the following expression

Sum({< Category = P({< Supplier = {'$(=Concat(DISTINCT Supplier, chr(39) & chr(44) & chr(39)))'} >}) >} Sales)

The Concat part will return in the proper format (single quoted, comma separated) the values for Supplier you have selected. If no selection is done, then all possible values for Supplier are passed and all information should be displayed.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Set Analysis question

Tue, 14 Jun 2011 14:42:12 GMT

Hi Miguel!

It is not exactly what I am looking for.

If using

Sum({< Category = P({< Supplier = {'$(=Concat(DISTINCT Supplier, chr(39) & chr(44) & chr(39)))'} >}) >} Sales)

I am getting 30 in sales when selecting supplier 1. I want it to be 41.

When selecting supplier 2 I want it to summarize to 58 (all shoes, balls and tennis).

I think we are close but not quite there.

Thanks a lot for your help, hope you have som emore suggestions.

Christer

Re: Set Analysis question

Miguel_Angel_Baeyens — Tue, 14 Jun 2011 14:55:36 GMT

Hi Christer,

Ok, let's go one step further:

Sum({< Supplier =, Category = P({< Supplier = {$(=Concat(DISTINCT Supplier, chr(44)))} >}) >} Sales)

In the set analysis modifier I now add the "Supplier =" that means "ignore the selection done in field Supplier". But it's later taken into account within the P() function.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

EDIT: I've removed the quoting in the Concat() function since Supplier are numeric codes. But if they are not in actual data, quoting would be required as in my previous expression above.

Re: Set Analysis question

Tue, 14 Jun 2011 15:13:44 GMT

Hi Christer,

Check the attached aplication.

- Sridhar

Set Analysis question

Tue, 14 Jun 2011 15:29:45 GMT

Thanks alot both of you!