topic Re: Need Summation logic at set analysis in QlikView

Need Summation logic at set analysis

agni_gold — Tue, 06 Dec 2016 14:59:35 GMT

Hi Friends,

I have a situation ,

Where i want to show only data for below condition

Negative MACO and Positive Vol , attaching my sample data also .

It is required to do this at frontend level.

Re: Need Summation logic at set analysis

MindaugasBacius — Tue, 06 Dec 2016 15:07:46 GMT

Maybe this could help?

Re: Need Summation logic at set analysis

vinieme12 — Tue, 06 Dec 2016 15:08:17 GMT

Try as below

sum({<Account = {'Maco'} , Value = {"<0"}>}Value)

sum({<Account = {'Vol'} , Value = {">0"}>}Value)

Re: Need Summation logic at set analysis

agni_gold — Tue, 06 Dec 2016 15:11:47 GMT

in a single condition , it is not possible ?

Re: Need Summation logic at set analysis

agni_gold — Tue, 06 Dec 2016 15:15:32 GMT

I need here , only to come one row for vol which is

A ------------ 3

Re: Need Summation logic at set analysis

vinieme12 — Tue, 06 Dec 2016 15:16:04 GMT

what exactly do you want to evaluate? please clarify

sum({<Account = {'Maco'} , Value = {"<0"}>}Value)

sum({<Account = {'Vol'} , Value = {">0"}>}Value)

Re: Need Summation logic at set analysis

agni_gold — Tue, 06 Dec 2016 15:18:32 GMT

I need here , only to come one row for vol which is

A ------------ 3

which is satisfying below condition

Negative MACO and Positive Vol

Re: Need Summation logic at set analysis

vinieme12 — Tue, 06 Dec 2016 15:21:48 GMT

SUM( if((Account = 'Maco' AND Value <0) OR (Account='Vol' and Value > 0 ),Value) )

sum({<Account = {'Maco'} , Value = {"<0"}>}Value)

sum({<Account = {'Vol'} , Value = {">0"}>}Value)

Re: Need Summation logic at set analysis

agni_gold — Tue, 06 Dec 2016 15:23:07 GMT

Not working

Re: Need Summation logic at set analysis

vinieme12 — Tue, 06 Dec 2016 15:28:58 GMT

Works for me

Re: Need Summation logic at set analysis

sunny_talwar — Tue, 06 Dec 2016 15:32:24 GMT

May be this:

Dimension

State

Expression

fabs(RangeSum(Sum({<Account = {'Vol'}, Value = {'>0'}>} Value), Sum({<Account = {'Maco'}, Value = {'<0'}>} Value)))

Re: Need Summation logic at set analysis

vinieme12 — Tue, 06 Dec 2016 15:32:48 GMT

This will also work, but you will need a keyfield to identify each datarow

sum({< KEY = p({<Account = {'Vol'} , Value = {"<0"}>}KEY)+p({<Account = {'Maco'} , Value = {">0"}>}KEY) >} Value)

Re: Need Summation logic at set analysis

agni_gold — Wed, 07 Dec 2016 06:50:57 GMT

Hi Sunny ,

result is

State	Test
	17
A	3
B	20

But i dont require B row , because it is not coming under my condition

Negative MACO and Positive Vol

Re: Need Summation logic at set analysis

vinieme12 — Wed, 07 Dec 2016 07:10:08 GMT

Hi ,can you explain the calculation for why not B?

+Vol = 23

-Maco = 3 , so 20

Re: Need Summation logic at set analysis

agni_gold — Wed, 07 Dec 2016 07:28:57 GMT

Hi ,

Initial Data

State	Account	Value
A	Vol	10
B	Vol	-2
B	Vol	23
A	Vol	-7
A	Maco	-1
A	Maco	-2
B	Maco	-3
A	Maco	-4
B	Maco	5
A	Maco	-6

Aggregated data based on State Column

State	Account	Value
A	Vol	3
B	Vol	21
A	Maco	-13
B	Maco	2

Now if we check my condition based in State Column ,

Example : For State A , Negative Maco and Positive Volume , Condition is true , and for B it is not , so when i am showing Volume data in expression , only A should come.

Re: Need Summation logic at set analysis

vinieme12 — Wed, 07 Dec 2016 07:34:02 GMT

OH K, Use

fabs(if(sum({<Account = {'Maco'}>}Value)<0, sum({<Account = {'Maco'},Value={'<0'} >}Value))

if(sum({<Account = {'Vol'}>}Value)>0, sum({<Account = {'Vol'},Value={'>0'} >}Value))

)

Re: Need Summation logic at set analysis

sunny_talwar — Wed, 07 Dec 2016 09:42:18 GMT

Another option (slightly modifiying Vineeth's above response)

fabs(Sum({<Account = {'Maco'},Value={'<0'}, State = {"=Sum({<Account = {'Maco'}>}Value)<0 and Sum({<Account = {'Vol'}>}Value)>0"}>}Value)

Sum({<Account = {'Vol'},Value={'>0'}, State = {"=Sum({<Account = {'Maco'}>}Value)<0 and Sum({<Account = {'Vol'}>}Value)>0"}>}Value))