2 Replies Latest reply: Aug 29, 2011 8:58 AM by Sally Hurley RSS

    Execute Set Analysis stored in a table

    Sally Hurley



      I have a series of complex set analys that need to be executed based on certain values in a table.  For example:



      MU1<DenomMeasureID={"MU1"}, OnsetDate={">=$(vPeriodStart)<=$(vPeriodEnd)"}>
      MU2<DenomMeasureID={"MU2"}, OnsetDate={">=$(vPeriodStart)<=$(=DATE('$(vPeriodEnd)' -4))"}, DenomType={"REQUESTEDPHI"}>
      MU3<NPCDate={">=$(vDenomStart)<=$(vPeriodEnd)"}, NPCDenomType={"Encounter"}, NPCDenomValue={$(vMU3EMCodes)} >


      I can dump the SetAnalysis value in a chart, but if I tried to do the following code, it gives me null or the entire set of IDs.  For example, the Dimension in the chart is KeyValue, and an Expression in the chart is:


      COUNT( DISTINCT { $(SetAnalysis) }  PatientID)


      If I replace the Expression with this:


      COUNT( DISTINCT { <DenomMeasureID={"MU1"}, OnsetDate={">=$(vPeriodStart)<=$(vPeriodEnd)"}> }  PatientID)


      It works fine (well... it give the same value for all rows, which is not what I'm trying to accomplish).


      I have tried a number of variations:

      COUNT( DISTINCT { $(=SetAnalysis) }  PatientID)

      COUNT( DISTINCT { SetAnalysis }  PatientID)


      And nothing gives me what I want.  What am I doing wrong?

        • Re: Execute Set Analysis stored in a table
          John Witherspoon

          Dollar sign expansion is done up front, outside of the context of your chart dimensions.  At that time, unless you've selected a single KeyValue, SetAnalysis has multiple values, and therefore returns null.  So then you probably just count the distinct PatientIDs, ignoring the set you wanted.


          If you avoid the dollar sign expansion by just sticking the field in there directly, it will have a unique value for SetAnalysis.  However, at that point, it will be a string, not something actually inserted into the code to be evaluated.  In other words, I think it would be the equivalent of this:


          COUNT( DISTINCT {'<DenomMeasureID={"MU1"}, OnsetDate={">=$(vPeriodStart)<=$(vPeriodEnd)"}>'}  PatientID)


          Where the single quotes make this a completely invalid expression, and so I would expect it to return null.  You just can't use field values this way.


          The evaluate() function would be perfect, except that it's explicitly disallowed in expressions.


          The best way I've found to get around these limitations (and I'm not saying it's a good way) is to generate a pick() expression that can then choose the correct expression to apply.  See attached example.  Script for the expression part and variable generation below.


          LOAD recno() as MetricSequence,* INLINE [
          Metric, Expression
          Count, count(Amount)
          Max, max(Amount)
          Total, sum(Amount)

          LOAD concat(Expression,',',MetricSequence) as AllExpressions
          RESIDENT Metric;

          LET vMetrics = 'pick(MetricSequence,' & peek('AllExpressions') & ')';

          DROP TABLE Metrics;