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

[#1]

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?

[#2]

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

Wk	Platform	Build	Test  Category	Test Type	Total	Pass	Fail	Crash	Skip	Run %	Pass%
34	test	40	unit	ntrace	82	82	0	0	0	1	1
34	test	40	unit	src	120	116	0	0	4	0.96667	0.9666667
34	test	39	unit	ntrace	82	82	0	0	0	1	1
34	test	39	unit	src	120	116	0	0	4	0.96667	0.9666667
34	test	38	unit	ntrace	82	82	0	0	0	1	1
34	test	38	unit	src	120	116	0	0	4	0.96667	0.9666667
34	test	37	unit	ntrace	82	82	0	0	0	1	1
34	test	36	unit	ntrace	82	82	0	0	0	1	1
34	test	36	unit	src	120	116	0	0	4	0.96667	0.9666667

[#3]

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]

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]

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]

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.

[#7]

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.

[#8]

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

[christian77]

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.

[#10]

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.