Null value showing up using aggr() function

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have a requirement where in I need to do a YTD calculation. Pasting a snapshot of the dataset below :

CO Number	Month	Failed Changes	Product
123	17-Jan	N	XYZ
456	17-Feb	N	XYZ
789	17-Mar		XYZ
111	17-Apr	N	XYZ
112	17-May	N	XYZ
113	17-Jun		XYZ
114	17-Jul		XYZ

The requirement is calculate a YTD percentage based on the number of Failed Changes. The total number of failed changes/the total number of changes for a particular product. So I am using the following expression for calculating: Aggr(RangeSum(Above(Count(DISTINCT{<[Failed change]={'Y'}>}[Change Order Number]), 0, RowNo())),Product) / RangeSum(Above(count(DISTINCT([Change Order Number])), 0, RowNo())) But as you can see from the dataset, there are certain entries that do not have the Failed changes value populated. So this expression returns a null value. Ideally the expression should exclude the changes marked with a 'N' or a null value.

Can anyone help in providing some suggestions over this ?

Re: Null value showing up using aggr() function

sunny_talwar — Fri, 20 Oct 2017 13:03:09 GMT

Would you be able to share a sample where you are trying to do this?

Re: Null value showing up using aggr() function

eduardo_dimperio — Fri, 20 Oct 2017 17:29:55 GMT

In script you coud do

TMP:

Load

[CO Number],

PRODUCT,

If(Isnull([Fail changes]) OR [Fail changes]='N',0,1) AS FAIL_FLAG

from YOURQVD

COUNTERS:

Load

[CO Number],

PRODUCT,

SUM(FAIL_FLAG)/COUNT(FAIL_FLAG)*100 AS RESULT

RESIDENT TMP

GROUP BY

[CO Number],

PRODUCT

topic Null value showing up using aggr() function in QlikView

Null value showing up using aggr() function

Re: Null value showing up using aggr() function

Re: Null value showing up using aggr() function