5 Replies Latest reply: Jan 31, 2012 7:34 PM by Graham Crooks RSS

    Multiple selection criteria in a set analysis?

    Graham Crooks

      Hi All

      I have an expression that covers some multiple selection criteria / options between the inter-related values of two fields, d01 and d02. As an example:

       

      sum(

      if(AdmitMethod<>'81'

      and ((d01 like 'I21*') or (d01 like 'I22*') or ((d01 like 'R07*') and ((d02 like 'I21*') or (d02 like 'I22*'))))

      ,AdmCounter))

       

      So, this works of course, but it's a little wieldly and will only get worse the more combinations of d01 and d02 I have to cover.

      Using a set analysis approach, so far I've arrived at:

       

      sum({<~AdmitMethod={'81'},d01Ch={'I21','I22','R07'}>}AdmCounter)

       

      where d01Ch is, usefully in this case, the 3 character version of d01 (which can be 4-6 characters long, the first 3 providing the grouping).

       

      As you can see the two expressions are different. I'm wondering if there's a neat and tidy way to incorporate the d01/d02 relationship for ... OR (d01 = R07 AND (d02=I21 OR d02=I22)) into the set analysis, whilst maintaining the 'd01Ch can equal I21 or I22' part.

      Can anyone suggest a way to achieve this please?

      All help and advice most welcome.

        • Multiple selection criteria in a set analysis?
          Perumal Ayyappan

          Hi

          Try this below expression

           

          Sum({<AdmitMethod-={'81'},d01= {'121*','122*','R07*'},d02= {'122*'}>}AdmCounter)

           

          Regards ,

          Perumal A

          • Multiple selection criteria in a set analysis?
            Celambarasan Adhimulam

            Hi,

                 Try this

                 sum({<AdmitMethod-={'81'},d01={'I21*','I22*','R07*'},d02={'121*','122*'}>} AdmCounter)

             

            Celambarasan

              • Multiple selection criteria in a set analysis?
                Graham Crooks

                Hi Perumal & Celambarasan - Thank you for your suggestions.

                As the various elements within the set analysis effectively form an AND statement, sadly this doesn't work; the determination effectively becomes 'if d01 = any of... AND if d02 = any of...'. The problem I have here is that if d01 is I21* or I22* then d02 certainly will not be; if d01 is R07, then d02 might be I21* or I22*. But being willing to try anything once, I've tried the suggestion. The data simply disappears as there is now no match. Any other suggestions would, however, be most welcome.

                  • Re: Multiple selection criteria in a set analysis?
                    Miguel Angel Baeyens de Arce

                    Hi Graham,

                     

                    What about using two (or more) set modifiers instead of just one?

                     

                    Sum({< AdmitMethod -= {'81'}, d01 = {"121*", "122*", "R07*"} > + < AdmitMethod -= {'81'},  d02 = {"122*"} >} AdmCounter)
                    

                     

                    That "+" sign means that the values of AdmCounter will be summed up when values correspond to either the first or the second modifier (things between <>). The above should return the same as

                     

                    Sum({< AdmitMethod -= {'81'} > * (< d01 = {"121*", "122*", "R07*"} > + < d02 = {"122*"} >)} AdmCounter)
                    

                     

                    That can be read as "return the sum of values in AdmCounter for the data set where AdmitMethod is not 81 and ("*") either d01 or d02 (or both) ("+") have the values specified".

                     

                    Hope that helps and makes sense.

                     

                    Miguel