topic Set Analysis Expression in QlikView

Set Analysis Expression

Anonymous — Sat, 05 Dec 2015 12:31:52 GMT

Hi,

Please find the dataset in image attached

i wanted calculate the Profit / loss of those script whose quantity is 0 (i.e (Sell Qty - Buy Qty) = 0). I could achieve this using the below statement

=SUM(if(([Sell Qty] - [Buy Qty])=0,(([Sell Qty]*[Sell Price]) - ([Buy Qty]*[Buy price]))))

But how to write this in Set Analysis.

Thanks in Advance,

With Regards,

Manoj S nair

Re: Set Analysis Expression

swuehl — Sat, 05 Dec 2015 12:40:57 GMT

Set analysis usually requires a somewhat knowledge of the fields and relation between fields and tables, so I can only guess what you might need here.

If script is the field name of your chart dimension, maybe try an advanced search on this field:

Sum( {<script = {"=[Sell Qty] - [Buy Qty]=0"} >} [Sell Qty]*[Sell Price]) - ([Buy Qty]*[Buy price])

Re: Set Analysis Expression

Anonymous — Sat, 05 Dec 2015 12:56:34 GMT

Thanks Swuehl. it worked

=Sum({<Scrip = {"=[Sell Qty] - [Buy Qty]=0"} >} ([Sell Qty]*[Sell Price]) - ([Buy Qty]*[Buy price]))

Re: Set Analysis Expression

Anonymous — Sat, 05 Dec 2015 13:03:12 GMT

Swuehl one more question

if i want to write two condition in set analysis

[Sell Price] - [Buy price]<0

[Sell Qty] - [Buy Qty] = 0

i tried this not working :

=Sum({<Scrip = {"=[Sell Price] - [Buy price]<0,=[Sell Qty] - [Buy Qty] = 0 "} >} ([Sell Qty]*[Sell Price]) - ([Buy Qty]*[Buy price]))

also can you suggest me good link for Set Analysis

Re: Set Analysis Expression

swuehl — Sat, 05 Dec 2015 13:34:58 GMT

You can try

=Sum( {<Scrip = {"= ([Sell Price] - [Buy price]<0) AND ([Sell Qty] - [Buy Qty] = 0)"} >} ([Sell Qty]*[Sell Price]) - ([Buy Qty]*[Buy price]))

There are some useful threads and posts available, e.g.

A Primer on Set Analysis‌

A Beginners' Introduction to Set Analysis (video)

Set Analysis: syntaxes, examples

Set Analysis Archives • AfterSync - QlikView Consulting