Null value showing up using aggr() function

[Anonymous]

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 ?

[sunny_talwar]

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

[eduardo_dimperio]

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