9 Replies Latest reply: Sep 9, 2011 12:45 PM by doschafe RSS

    How can you enhance an expression such that a missing test set is not considered as 0 in a minimum statment?

      We are calculating test result information.

       

      We have a nested if statement using GetCurrentField and one nesting is as follows:

       

      if(GetCurrentField(TestDrillIn)='BuildBotStep'

             ,

              (min(aggr(   SUM(Skip),BuildBotStep,Build))),

       

      This works fine except in one case.  The case is such that there is a test build (run) where for the BuildBotStep dimension, one of its categories was not run.  The rollup of the these calculations considers that as 0 for minimum, how do I consider the no test run row as something other than 0 to the dimensions higher than the one that exists?

        • Re: How can you enhance an expression such that a missing test set is not considered as 0 in a minimum statment?

          Look at this table data, the 8th row of data s/b a row with type 'src'  (Build 37 did not run the 'src' tests, you can see the row that is not there that I perceive Qlikview is counting as zero for dimensions above BuildBotStep for the min expression, any ideas?:

           

          WkPlatformBuildTest  CategoryTest TypeTotalPassFailCrashSkipRun % Pass%
          34test40unitntrace828200011
          34test40unitsrc1201160040.966670.9666667
          34test39unitntrace828200011
          34test39unitsrc1201160040.966670.9666667
          34test38unitntrace828200011
          34test38unitsrc1201160040.966670.9666667
          34test37unitntrace828200011
          34test36unitntrace828200011
          34test36unitsrc1201160040.966670.9666667
          • How can you enhance an expression such that a missing test set is not considered as 0 in a minimum statment?
            Christian Conejero

            You may want to consider changing those values by the average of the others.

            That is the best estimation you can do in an easy way.

            ok.

            • Re: How can you enhance an expression such that a missing test set is not considered as 0 in a minimum statment?

              Got around this by excluding that test set from result.  thanks.

              • Re: How can you enhance an expression such that a missing test set is not considered as 0 in a minimum statment?

                The answer to solve this issue with aggr functions on drill in so that minimum correctly does not include dimensions that have missing categories resides in the proper placement of the if statement.  If you put the if statement inside the SUM statement, you will get 0 for min if no category exists for a dimension but if you put the SUM statement inside the if statement, those dimension with missing categories correctly show -

                 

                Example:

                 

                if(GetCurrentField(TestDrillIn)='Wk'

                          ,

                          // (min(aggr(   SUM(if(Build=$(vBestBuildPct),Skip,100)),Build))),   

                          (min(aggr(   if(Build=$(vBestBuildPct),SUM(Skip)),Build))),   

                 

                                    if(GetCurrentField(TestDrillIn)='PlatformName'

                                    ,

                                              //(min(aggr(   SUM(if(Build=$(vBestBuildPct),Skip,100)),PlatformName,Build))),

                                              (min(aggr(   if(Build=$(vBestBuildPct),SUM(Skip)),PlatformName,Build))),

                 

                                                        if(GetCurrentField(TestDrillIn)='DeviceName'

                                                        ,

                                                                  // (min(aggr(   SUM(if(Build=$(vBestBuildPct),Skip,100)),DeviceName,Build))),

                                                                  (min(aggr(   If(Build=$(vBestBuildPct),SUM(Skip)),DeviceName,Build))),

                                                                     

                                                                            if(  GetCurrentField(TestDrillIn)='TestCategory'

                                                                            ,

                                                                                       // (min(    aggr(   SUM(if(  Build=$(vBestBuildPct),Skip,100)),TestCategory, Build))) ,      

                                                                                      (min(aggr(   If(Build=$(vBestBuildPct), SUM(Skip)),TestCategory, Build))), 

                 

                 

                                                                                      if(GetCurrentField(TestDrillIn)='BuildBotStep'

                                                                                      ,

                                                                                      //           (min(aggr(   SUM(if(Build=$(vBestBuildPct),if(Pass>=0,Skip, -1))), BuildBotStep,Build))),

                                                                                      //          (min(aggr(   SUM(if(Build=$(vBestBuildPct),Skip,100)), BuildBotStep,Build))),

                                                                                      (min(aggr(   if(Build=$(vBestBuildPct),SUM(Skip)), BuildBotStep,Build))),

                 

                                                                                                if(GetCurrentField(TestDrillIn)='Build'

                                                                                                ,

                                                                                                          //(min(aggr(   SUM(if(Build=$(vBestBuildPct),Skip,100)),Build)))

                                                                                                          (min(aggr(   if(Build=$(vBestBuildPct),SUM(Skip)),Build)))

                 

                 

                          ))))))

                 

                Have not tried Christian suggestion of switching to sets.