topic Re: intermediate aggregation without setting nulls to zero in QlikView

intermediate aggregation without setting nulls to zero

Wed, 25 Nov 2020 16:16:04 GMT

I have some raw data that needs to be aggregated by "id" which is not a dimension in the chart, and then summed with the absolute of val and val2 individually. The concept is sum of absolute of individual values is not the same as absolute (sum(values)). Some Id's have zero value and null values. Null indicates that that id should not be counted.

gr	adate	id	val	val2
A	'11/18/2015'	1	5	-5
A	'11/17/2015'	2	-2	3
A	'11/17/2015'	2	5	-5
A	'11/16/2015'	3	0	3
A	'11/13/2015'	5	-6	6
A	'11/11/2015'	6	Null	-2
A	'11/10/2015'	7	1	-1
B	'11/18/2015'	1	7	-7
B	'11/17/2015'	2	-9	Null
B	'11/17/2015'	2	4	Null
B	'11/13/2015'	3	-8	0
B	'11/13/2015'	3	7	0
B	'11/13/2015'	3	11	0
B	'11/12/2015'	4	Null	-6

For group A, it should report cnt=numericcount(id)=5 and sum(fabs(val))=15 and not 19, because for id=2 fabs(val)=fabs(-2+5)=3.

Possible solutions:

1. on raw data cnt=numericcount(id) will work. But sum will not

2. if I do load from resident with a group by then null becomes zero, I lose the count but sum is okay.

Thanks.

Re: intermediate aggregation without setting nulls to zero

swuehl — Wed, 30 Mar 2016 16:50:52 GMT

You want to do this in the script or chart?

In a chart with dimension gr, maybe like

=Sum( Aggr( Fabs(Sum(val)), gr, id))

Re: intermediate aggregation without setting nulls to zero

sunny_talwar — Wed, 30 Mar 2016 16:54:56 GMT

These may be:

=Count({<Key = {"=Avg(fabs(val)) > 0"}>}id)

=Sum(Aggr(fabs(Sum(val)), id, gr))

Re: intermediate aggregation without setting nulls to zero

Wed, 30 Mar 2016 17:48:51 GMT

The count for B needs to be 3, because ids are 1,2, 3 and 4 but 4 has val=Null.

The data can have multiple rows of 4 with Null.

also the average sum of 0 is valid, so cannot exclude them.

I tried several things. It seems that we do aggr() on id/gr then exclude sum that equals Null. But I think sum considers Null to be zero, that is another issue.

Thanks for the help

Re: intermediate aggregation without setting nulls to zero

Wed, 30 Mar 2016 17:59:09 GMT

I thought the following would work, but returns 0 --

count( Aggr( Fabs(Sum({${<val-={'Null'}>} val)), gr, id))

Re: intermediate aggregation without setting nulls to zero

sunny_talwar — Wed, 30 Mar 2016 18:09:42 GMT

What are you hoping to see for count and Sum for both A and B?

Re: intermediate aggregation without setting nulls to zero

maxgro — Wed, 30 Mar 2016 18:30:47 GMT

maybe a

Count(distinct if(val<>'Null', id))

Re: intermediate aggregation without setting nulls to zero

Wed, 30 Mar 2016 21:11:56 GMT

Thank you all of you mighty guys, in particular, maxgro for the correct suggestion for Count() issue, swuehl and Sunny T for the Sum() issue.

The right combination is

Count(distinct if(val<>'Null', id))

and

=Sum( Aggr( Fabs(Sum(val)), gr, id)) // same result

=Sum( Aggr( Fabs(Sum(val)), id, gr)) //same result