topic Re: Set Analysis with Aggr and Alternate States in QlikView

Set Analysis with Aggr and Alternate States

cbaqir — Wed, 20 May 2015 20:51:31 GMT

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

Re: Set Analysis with Aggr and Alternate States

JonnyPoole — Wed, 20 May 2015 20:56:27 GMT

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]>}

,dimensions)

)

Re: Set Analysis with Aggr and Alternate States

cbaqir — Wed, 20 May 2015 21:15:04 GMT

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))

Re: Set Analysis with Aggr and Alternate States

JonnyPoole — Thu, 21 May 2015 02:36:00 GMT

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)

)

Re: Set Analysis with Aggr and Alternate States

cbaqir — Thu, 21 May 2015 16:15:28 GMT

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

Re: Set Analysis with Aggr and Alternate States

cbaqir — Thu, 21 May 2015 16:45:56 GMT

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))

Re: Set Analysis with Aggr and Alternate States

JonnyPoole — Thu, 21 May 2015 17:32:25 GMT

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 ?

Re: Set Analysis with Aggr and Alternate States

cbaqir — Thu, 21 May 2015 19:33:10 GMT

Sample attached

Re: Set Analysis with Aggr and Alternate States

JonnyPoole — Mon, 25 May 2015 14:24:54 GMT

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.

Re: Set Analysis with Aggr and Alternate States

cbaqir — Tue, 26 May 2015 15:42:21 GMT

Thank you Jonathan!