Need Summation logic at set analysis

[agni_gold]

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.

[sunny_talwar]

May be this:

Dimension

State

Expression

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

[vinieme12]

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)

[agni_gold]

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

[vinieme12]

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

+Vol = 23

-Maco = 3 , so 20

[agni_gold]

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.

[vinieme12]

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

)

[sunny_talwar]

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