Nested If or Aggr query

[#1]

Hi All,

I hope someone might be able to help me as I have been struggling with an expression. I have tried both a Sum(If and an aggregated sum but cant get it to do quite what I want.

I have a set of data as below

InNumber	FormKey	101FormNo	Counter
1107251157	1912129		1
1107251157	1912132		1
1107251157	1927766		1
1107251157	1931821	747106	1
1107251157	1933693		1
1107251157	1935247	747106	1
1107251157	1992536	747105	1
1107251157	2027356	747104	1
1107251157	2027357	747107	1

I need to do a count of the 101FormNo column for each InNumber which as it stands would return 5, my problem is that if the InNumber has more than 1 101FormNo's against it I only want to return the value 1. I just need to know if there is a value for each InNumber and not how many there are or when I try to do a % completion formula instead of getting 100% to say there is a match I get 500% which when you expand this to the full dataset causes the figure to be skewed.

Any help would be appreciated

Thanks

[swuehl · Accepted solution]

Maybe like

=sum(aggr( if(count([101FormNo]),1,0) ,InNumber))

[#3]

Thanks for you help that worked really well. My calculations look much better now.