Position Within Group

[lukegilligan]

I am trying to capture against a subgroup within a group that resets to 1 every time the group changes.

For example:

Family Code	Name	Relationship
1	Bob	Primary
1	Jane	Partner
1	Rob	Dep Child
2	Fred	Primary
3	Amy	Primary
3	Parker	Dep Child
3	Elyse	Friend
3	Shane	Friend
3	Linkin	Friend
4	Brad	Primary
4	Shelley	Partner
5	Sam	Primary
5	Olivia	Friend

What I need is an additional field that captures if their relationship is Primary, or Friend, and increases the counter within the family every time this happens, like in the table below:

Family Code	Name	Relationship	Position
1	Bob	Primary	1
1	Jane	Partner	1
1	Rob	Dep Child	1
2	Fred	Primary	1
3	Amy	Primary	1
3	Parker	Dep Child	1
3	Elyse	Friend	2
3	Shane	Friend	3
3	Linkin	Friend	4
4	Brad	Primary	1
4	Shelley	Partner	1
5	Sam	Primary	1
5	Olivia	Friend	2

Any help would be much appreciated.

[YoussefBelloum · Accepted solution]

Hi,

Add this line into your load:

if(Relationship='Friend',AutoNumber(RowNo(),[Family Code])+1,1) as Position

PFA

[YoussefBelloum]

And maybe stalwar1 can give you another alternative using only frond-end expression.

Tried with rangesum & above but I was stuck..

[sunny_talwar]

Something like this

=Aggr(If(Relationship = 'Friend', RangeSum(Above(TOTAL Column(1)), 1), 1), RowNum)

But I suggest this to be handled in script just like you did youssef

[YoussefBelloum]

Thanks Buddy, you rock !

here you go Luke, good luck

[lukegilligan]

Such an elegant solution that worked perfectly, thank you so much sir, you have made my day!!

Thanks again

[YoussefBelloum]

You're welcome, good luck