Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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_NamePERIODDep_ControlSub_IDControl_IDControl_VALUE
A20131211aC0A01400
A20160901aC0A01-600
A20131201bC0B01500
A20131201cC0B012
A20160911c
A20131211dC0C0270
B20131201aC0A0130
B20131201aC0B02-560
B20160911aC0B01370
B20131201bC0C01400
B20160901bC0C01230

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

Dep_NamePERIODSub_IDControl_IDControl_VALUE
A2013121aC0A01-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

1 Solution

Accepted Solutions
antoniotiman
Master III
Master III

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

View solution in original post

2 Replies
antoniotiman
Master III
Master III

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

Not applicable
Author

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