Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Null value showing up using aggr() function

Hi All,

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

   

CO NumberMonthFailed ChangesProduct
12317-JanNXYZ
45617-FebNXYZ
78917-MarXYZ
11117-AprNXYZ
11217-MayNXYZ
11317-JunXYZ
11417-JulXYZ

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 ?

2 Replies
sunny_talwar

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

eduardo_dimperio
Specialist II
Specialist II

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