Set Analysis Aggr?

[cbaqir]

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!

[sunny_talwar]

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)

[cbaqir]

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?

[sunny_talwar]

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

[cbaqir]

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)

[Digvijay_Singh]

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)

[jonathandienst]

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.

[Kushal_Chawda]

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)

[sasiparupudi1]

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

[#10]

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)