11 Replies Latest reply: Mar 11, 2016 9:50 AM by Jan Hagelauer RSS

    Set Analysis Aggr?

    Cassandra Baqir

      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

          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?
              Cassandra Baqir

              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

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

                    • Re: Set Analysis Aggr?
                      Cassandra Baqir

                      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

                          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?
                            Jonathan Dienst

                            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?
                              Jan Hagelauer

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