Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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?

9 Replies
Not applicable

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
Not applicable

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

1. See the attached qlikview Chart 2 - Grid and Chart 2 - Graph - Notice no Skip

2. Click Wk34 - Notice no Skip

3. Click unit  - Now you see some Skip

This seems to come from a test run where the tests "src" were not run and no row exists.  But, when in dimensions above BuildBotStep, this is not noticed for the min aggregate but is noticed when using the max aggregate so it seems the empty row or whatever it is counts as 0.

How to work around?

Thanks,

D

qliksus
Valued Contributor

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

hi,

In the column BuildBotStep eventhough the row "src" is not there u want to show it in the chart

Am i right in understanding ur requirement

christian77
Valued Contributor

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

Hi:

For display, show / don't show ceros in table. Properties > Presentation.

For calculations:

avg({$<Field={'<>0'}>} Sales)      --> does not take 0 values

avg({$<Field={'*'}>} Sales)   --> does not take null values

christian77
Valued Contributor

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

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.

Not applicable

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

Well, not exactly.  I don't want the missing row to be considered as zero in the min statement when being viewed by dimensions above it.

Not applicable

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.

christian77
Valued Contributor

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

It does not consider them as zero, doscafes.

avg({$<Field={'<>0'}>} Sales)      --> does not take 0 values.

(1+2+3+0)/4 becomes (1+2+3)/3

Ok.

Not applicable

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.

Community Browser