topic Re: Set Analysis Aggr? in QlikView

Set Analysis Aggr?

cbaqir — Thu, 10 Mar 2016 21:49:05 GMT

This text box expression is almost (but not quite) right...

=Count(distinct {$<CUR_WORKFLOW_STEP_NAME={'Project Initiation and Design'},REQUEST_ID ={"=sum(WORKFLOW_DURATION)>120"}>}REQUEST_ID)

I am trying to show the count of distinct request IDs where the CUR_WORKFLOW_STEP_NAME = 'Project Initiation and Design' and the sum(WORKFLOW_DURATION)>120. The catch is that a request may have been in Project Initiation and Design before, come out of it and is now back in it. That would mean, I think, taking the aggr sum of sum(WORKFLOW_DURATION)>120 for all rows in which WORKFLOW_STEP_NAME = 'Project Initiation and Design'

I believe I need to add an aggr before this part sum(WORKFLOW_DURATION)>120...

=Count(distinct {$<CUR_WORKFLOW_STEP_NAME={'Project Initiation and Design'},
REQUEST_ID ={"=AGGR(sum({$<WORKFLOW_STEP_NAME={Project Initiation and Design}>}WORKFLOW_DURATION)>120"}>}REQUEST_ID)

I was reading https://community.qlik.com/blogs/qlikviewdesignblog/2016/03/07/set-analysis-in-the-aggr-function and just can't figure out what's wrong.

Thanks!

Re: Set Analysis Aggr?

sunny_talwar — Thu, 10 Mar 2016 21:51:37 GMT

Not 100% sure, but can you try this:

=Count(distinct {$<CUR_WORKFLOW_STEP_NAME={'Project Initiation and Design'},REQUEST_ID ={"=Sum({$<CUR_WORKFLOW_STEP_NAME={'Project Initiation and Design'}>}WORKFLOW_DURATION)>120"}>}REQUEST_ID)

Re: Set Analysis Aggr?

cbaqir — Thu, 10 Mar 2016 21:54:57 GMT

There are two similar names that mean different things.

CUR_WORKFLOW_STEP_NAME = Current step name

WORKFLOW_STEP_NAME = any step in workflow name

The catch is that a request may have been in Project Initiation and Design before(WORKFLOW_STEP_NAME ) , come out of it and is now back in it (CUR_WORKFLOW_STEP_NAME and WORKFLOW_STEP_NAME ). If that is the case, the 2 WORKFLOW_DURATION values need to be summed.

Wouldn't that require an AGGR?

Re: Set Analysis Aggr?

sunny_talwar — Thu, 10 Mar 2016 21:56:46 GMT

Sorry din't realized they were two different name. Try the one you specified but with Aggr()

Re: Set Analysis Aggr?

cbaqir — Thu, 10 Mar 2016 22:00:09 GMT

I must be missing something. It says OK but doesn't give me a result.

=Count(distinct {$<CUR_WORKFLOW_STEP_NAME={'Project Initiation and Design'},REQUEST_ID ={"=aggr(Sum({$<WORKFLOW_STEP_NAME={'Project Initiation and Design'}>}WORKFLOW_DURATION)>120"}>}REQUEST_ID)

Re: Set Analysis Aggr?

Digvijay_Singh — Fri, 11 Mar 2016 03:01:07 GMT

I think you missed to specify dimension of Aggr and $ in the set element, but not sure what it should be, may be like this -

=Count(distinct {$<CUR_WORKFLOW_STEP_NAME={'Project Initiation and Design'},REQUEST_ID ={"=$(=aggr(Sum({$<WORKFLOW_STEP_NAME={'Project Initiation and Design'}>}WORKFLOW_DURATION),WORKFLOW_STEP_NAME))>120"}>}REQUEST_ID)

Re: Set Analysis Aggr?

jonathandienst — Fri, 11 Mar 2016 06:22:21 GMT

I suggest that you post a small sample with some representative data.

You might want to load the workflow steps as dual values, so they can be sorted and compared. This could be done by inline loading the status and using an Applymap during the normal load.

Re: Set Analysis Aggr?

Kushal_Chawda — Fri, 11 Mar 2016 07:01:26 GMT

try it with small script change.

Load WORKFLOW_STEP_NAME,

sum(WORKFLOW_DURATION) as WORKFLOW_DURATION_test

Resident YourTable

group by WORKFLOW_STEP_NAME;

Now try set expression

Count(distinct {$<CUR_WORKFLOW_STEP_NAME={'Project Initiation and Design'},REQUEST_ID ={"=Sum({$<WORKFLOW_STEP_NAME={'Project Initiation and Design'}>}WORKFLOW_DURATION_test)>120"}>}REQUEST_ID)

Re: Set Analysis Aggr?

sasiparupudi1 — Fri, 11 Mar 2016 07:47:07 GMT

Put this in a text box and see if it returns any value?

=aggr(Sum({$<WORKFLOW_STEP_NAME={'Project Initiation and Design'}>}WORKFLOW_DURATION),WORKFLOW_STEP_NAME))

Re: Set Analysis Aggr?

Fri, 11 Mar 2016 08:58:38 GMT

As far as I know, it is not possible to specify a filtering criterion on an aggregated value in set expressions.

You could get the number you want however by using a chart and filtering out the unwanted REQUEST_IDs using a dimension.

The dimension formula should be like this:

=if(aggr(Sum({$<WORKFLOW_STEP_NAME={'Project Initiation and Design'}>}WORKFLOW_DURATION), REQUEST_ID) > 120, 'yes')

Then set the dimension to "Suppress When Value Is Null", and hide the column on the Presentation tab.

The Expression should have this formula:

=Count(distinct {$<CUR_WORKFLOW_STEP_NAME={'Project Initiation and Design'}>}REQUEST_ID)

Re: Set Analysis Aggr?

cbaqir — Fri, 11 Mar 2016 14:44:09 GMT

This is a KPI in a text box, not a chart if that makes a difference.

Re: Set Analysis Aggr?

Fri, 11 Mar 2016 14:50:20 GMT

That shouldn't be a problem - if you use a straight table, hide the caption (Caption -> Show Caption") as well as the Table headers (Presentation -> Suppress Header Row) and deactivate the expression totals (Expressions -> Total Mode = No Totals), you'll end up with a single value cell that you can present like a text box (so the user won't notice the different object type in the background).