Set Analysis with Aggr and Alternate States

[cbaqir]

I currently have an expression for an alternate state that looks like this:

=avg({[Group A]< [PROJ_PROP_CAT] = $ ::[PROJ_PROP_CAT] , [REQ_STATUS] = $ ::[REQ_STATUS], [FACILITY] = $ ::[FACILITY]>} WORKFLOW_DURATION)

I need to modify the expression to still use the Alternate state (including ignoring filter selections as stated above) but using this expression instead of avg(WORKFLOW_DURATION): avg({$}Aggr(Sum(WORKFLOW_DURATION), REQUEST_ID, WORKFLOW_STEP_NAME))

I just can't seem to get the syntax correct. Any help would be appreciated.

Thanks!

Cassandra

[JonnyPoole]

Try putting the SET statement

({[Group A]< [PROJ_PROP_CAT] = $ ::[PROJ_PROP_CAT] , [REQ_STATUS] = $ ::[REQ_STATUS], [FACILITY] = $ ::[FACILITY]>}

in the Sum() function as well as the exterior avg() function.

Roughly something like this... set statements work on chart aggregation functions and both levels need the right context set explicitly:

Avg (

     ({[Group A]< [PROJ_PROP_CAT] = $ ::[PROJ_PROP_CAT] , [REQ_STATUS] = $ ::[REQ_STATUS], [FACILITY] = $ ::[FACILITY]>}

     aggr(

     sum(

({[Group A]< [PROJ_PROP_CAT] = $ ::[PROJ_PROP_CAT] , [REQ_STATUS] = $ ::[REQ_STATUS], [FACILITY] = $ ::[FACILITY]>}

     <expression>

,dimensions)

)

[cbaqir]

Jonathan,

Thanks for the quick response. I'm having trouble applying the expression and dimensions to the syntax above.

I am using a stacked bar chart for these expressions with dimensions PROJ_PROP_CAT and WORKFLOW_STEP_NAME.

Could you clarify what is meant by "both levels need the right context set explicitly" and/or please tell me what I am doing wrong?

My expression is: avg({$}Aggr(Sum(WORKFLOW_DURATION), REQUEST_ID, WORKFLOW_STEP_NAME))  I believe you are saying that it should be something like avg({$}Aggr(Sum(WORKFLOW_DURATION), PROJ_PROP_CAT, WORKFLOW_STEP_NAME))

[JonnyPoole]

Here is the same expression with the set statement from your current expression added into to both levels of aggregation

avg( ({[Group A]< [PROJ_PROP_CAT] = $ ::[PROJ_PROP_CAT] , [REQ_STATUS] = $ ::[REQ_STATUS], [FACILITY] = $ ::[FACILITY]>}

     Aggr(

          Sum(   ({[Group A]< [PROJ_PROP_CAT] = $ ::[PROJ_PROP_CAT] , [REQ_STATUS] = $ ::[REQ_STATUS], [FACILITY] = $ ::[FACILITY]>}   WORKFLOW_DURATION),

      REQUEST_ID, WORKFLOW_STEP_NAME)

)

[cbaqir]

Thanks. I am getting error in expression but I will work on it.

[cbaqir]

Is this the same thing?

=avg(Aggr(

{[Group A]< [PROJ_PROP_CAT] = $ ::[PROJ_PROP_CAT], [REQ_STATUS] = $ ::[REQ_STATUS], [FACILITY] = $ ::[FACILITY]>}

sum(WORKFLOW_DURATION),

REQUEST_ID,  PROJ_PROP_CAT, WORKFLOW_STEP_NAME))

[JonnyPoole]

Those expressions are syntactically incorrect.  You can't use a SET statement with aggr() , only with chart aggregation functions like sum() , count() etc...

Can you upload your sample ?

[cbaqir]

Sample attached

[JonnyPoole · Accepted solution]

Thank you .  i changed the expression to source the set statement in the avg() as well as the sum(). 

There is a bug in the expression editor that mistakingly underlines the expression as inaccurate but if you notice it also says 'expression ok' and does work.

Let me know if this gives you the right numbers.  I haven't analyzed the data values for validity , i just put in the expression i was articulating above.

[cbaqir]

Thank you Jonathan!