2 Replies Latest reply: Oct 13, 2017 10:59 AM by Janus Yuan

# sum set analysis

Hi all,

I am trying to extract data in the following way:

1. Within the same Dep_Name, Sub_ID, and Control_ID, we can only have two transactions. Otherwise we don't consider those transactions.
2. Of the two transactions we need to have PERIOD = 201312 and at the same time Dep_Control = 1, the other period with the same Dep_Name, Sub_ID, and Control_ID would be 201609, but Dep_Control has to be 0
3. If 1 and 2 are satisfied, get the negative  amount of Control_VALUE for the transaction with PERIOD = 201312

we would not have repetitive periods within the same Dep_Name, Sub_ID, and Control_ID

Example below

 Dep_Name PERIOD Dep_Control Sub_ID Control_ID Control_VALUE A 201312 1 1a C0A01 400 A 201609 0 1a C0A01 -600 A 201312 0 1b C0B01 500 A 201312 0 1c C0B01 2 A 201609 1 1c A 201312 1 1d C0C02 70 B 201312 0 1a C0A01 30 B 201312 0 1a C0B02 -560 B 201609 1 1a C0B01 370 B 201312 0 1b C0C01 400 B 201609 0 1b C0C01 230

we would only have 1st row so the table should only have 1 record

 Dep_Name PERIOD Sub_ID Control_ID Control_VALUE A 201312 1a C0A01 -400

I was able to make it work when selecting individual Dep_Name but it was not working without the selection.

=-1*Sum({\$<

Dep_Name={'=count(distinct Dep_Control)=2'},

Control_ID={'=count(distinct Dep_Control)=2'},

Sub_ID={'=count(distinct Dep_Control)=2'},

PERIOD={\$(MinPeriod)}

>} Control_VALUE)

MinPeriod = 201312

Thanks,

Janus

• ###### Re: sum set analysis

Hi Janus,

try this in Expression

=Aggr(If(Count(Sub_ID)=2,Only({<PERIOD={'\$(MinPeriod)'},Dep_Control={1}>} Control_VALUE)),Dep_Name,Sub_ID)

If You need then add - sign

Regards,

Antonio

• ###### Re: sum set analysis

Brilliant! It's working perfectly with my dataset. Another solution here.

=Aggr(If(Count(distinct Dep_Control)=2,Only({<PERIOD={'\$(MinPeriod)'},Dep_Control={1}>} -1*Control_VALUE)),Dep_Name,Sub_ID, Control_ID