2 Replies Latest reply: Jul 7, 2017 7:47 AM by Jason Campbell RSS

    Set analysis w/row Aggr help

    Jason Campbell

      In another thread, I was able to (successfully, I think) match two conditions using set analysis and aggr.  Now, I've been at what I think is the final expression for a couple of days and can't figure it out.  Reaching out for some help.

       

      The prior expression: If two conditions within a variable date range match, then sum the count.

      if(aggr(only({<datekey={"$(=Date(vdatekey))"}>}TelephoneNum),TelephoneNum) =

        aggr(only({<datekey={"$(=Date(vdatekey - 1))"}>}TelephoneNum),TelephoneNum) or

        aggr(only({<datekey={"$(=Date(vdatekey))"}>}TelephoneNum),cName) =

        aggr(only({<datekey={"$(=Date(vdatekey - 1))"}>}TelephoneNum),cName),

        sum({<datekey={"$(=Date(vdatekey - 1))"}>}Ct),0)

       

      The expression I need help with: If the two conditions within a variable date range match AND the count = 0 then 1/1 else 0/1.

      If(aggr(only({<datekey={"$(=Date(vdatekey))"}>}TelephoneNum),TelephoneNum) =

        aggr(only({<datekey={">=$(vTmaxdate) <=$(vTmindate)"}>}TelephoneNum),TelephoneNum) or

        aggr(only({<datekey={"$(=Date(vdatekey))"}>}TelephoneNum),cName) =

        aggr(only({<datekey={">=$(vTmaxdate) <=$(vTmindate)"}>}TelephoneNum),cName) and

        sum({<datekey={">=$(vTmaxdate) <=$(vTmindate)"}>}Ct)=0,1/1,0/1)

       

      Note: I also created a model with a key to avoid the multiple matches and try to use intersections.  Couldn't get it to work.  If this is the more practical way, I would love to use it.  Here's an expression I tried: (datakey is the concatenated key)

      sum({<datakey=P(datakey),datekey={"$(=Date(vdatekey)-1)"}>} Ct)

       

      fcr7.PNG

       

      Any and all help will be greatly appreciated.

       

      Thanks,
      Jason