5 Replies Latest reply: Oct 9, 2013 9:09 AM by Mina Patel

# Set Analysis

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

Hi

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

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

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

I have posted my example on my post above.

thanks

• ###### Re: Set Analysis

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