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

    sum set analysis

    Janus Yuan

      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