topic Re: Set Analysis in QlikView

Set Analysis

qlikmpate0 — Tue, 08 Oct 2013 13:54:36 GMT

I'm using set analysis to extract the value of one sales order but my joining tables do not bring back the relating information I wish to display.

This expression works correctly:- sum({<[Oderlines.Ship Month]=>}[Order amount])+sum({$sum<[Orderlines.Ship Month]=>}[frieght Amount])

This expression does work to some extent ([Order Amount])+ sum([Freight Amount]) , but when the detail is bought in not all the data is retrieved, therefore the set analysis approach was taken.

can anyone tell my why when using this expresion sum{1}([Order Amount])+ sum([Freight Amount]) affects the results, ie brings in all the records not the relating records.

any help would be appreciated, as I'm very confused.com

Message was edited by: Mina Patel Please find attached an example document, I was also wondering is it better doing the set analysis in the load acript or as an expression?

Re: Set Analysis

jonathandienst — Tue, 08 Oct 2013 14:10:33 GMT

The expression you posted here is not valid syntax, but perhaps that was a typo when posting.

I am not sure what you are asking, but based on the expression, perhaps you mean this:

sum({1} [Order Amount]) + sum({1} [Freight Amount])

which sums orders and freight disregarding all selections, but respecting dimensions if used in a chart.

Hope that helps

Jonathan

Re: Set Analysis

qlikmpate0 — Tue, 08 Oct 2013 14:18:19 GMT

Hi Jonathan,

Thanks for your prompt reply, I have tried this but still brings in unrelated records.

I'm trying to get the order amount for only a specifc order number, the qvd's I'm using has the information. My loader script does not have any restrictions, the only thing I can think of is when I bring in the header records i'm calculating the Fright amount at theis level, then bringing in the related lines.

Hope this makes sense.

Re: Set Analysis

Tue, 08 Oct 2013 14:19:28 GMT

Hi,

{1} means everything

{$} means current selection

A document (tutorial on Set Analysis) I have written few days ago may be helpfull:

http://community.qlik.com/docs/DOC-4951

Fabrice

Re: Set Analysis

swuehl — Tue, 08 Oct 2013 14:19:45 GMT

Yes, I am also unsure what you are trying to achieve. Could you maybe post a small sample that demonstrates your issue?

In general, HIC posted a recent blog post that explains quite fundamental things:

http://community.qlik.com/blogs/qlikviewdesignblog/2013/09/09/aggregation-scope

And you find more explanations about set analysis in the HELP, or here or here.

Re: Set Analysis

qlikmpate0 — Wed, 09 Oct 2013 13:08:53 GMT

I have posted my example on my post above.

thanks