intermediate aggregation without setting nulls to zero

[#1]

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.

[swuehl]

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))

[sunny_talwar]

These may be:

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

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

[#4]

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

[#5]

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

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

[sunny_talwar]

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

[maxgro]

maybe a

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

[#8 · Accepted solution]

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

or

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