Set Analysis Expression

[Anonymous]

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

[swuehl · Accepted solution]

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])

[Anonymous]

Thanks Swuehl. it worked

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

[Anonymous]

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

[swuehl]

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