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

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

Thu, 25 Aug 2011 20:52:51 GMT

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?

Thu, 25 Aug 2011 21:38:23 GMT

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

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

Thu, 25 Aug 2011 23:09:59 GMT

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,

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

qliksus — Fri, 26 Aug 2011 11:14:28 GMT

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

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

christian77 — Fri, 26 Aug 2011 12:00:52 GMT

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

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

christian77 — Fri, 26 Aug 2011 12:07:26 GMT

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?

Fri, 26 Aug 2011 13:51:58 GMT

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.

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

Fri, 26 Aug 2011 15:56:56 GMT

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?

christian77 — Mon, 29 Aug 2011 06:04:29 GMT

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.

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

Fri, 09 Sep 2011 16:45:37 GMT

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.