sum set analysis

Fri, 13 Oct 2017 00:21:13 GMT

Hi all,

I am trying to extract data in the following way:

Within the same Dep_Name, Sub_ID, and Control_ID, we can only have two transactions. Otherwise we don't consider those transactions.
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
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

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

Re: sum set analysis

antoniotiman — Fri, 13 Oct 2017 05:50:16 GMT

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

Fri, 13 Oct 2017 14:59:12 GMT

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)