sum set analysis

[#1]

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

Can you please help?

Thanks,

Janus

[antoniotiman · Accepted solution]

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

[#3]

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)