Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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 |
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
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
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
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.
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.
Got around this by excluding that test set from result. thanks.
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.
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.