Count based on multiple columns

[jt422605]

Hi,

I need to count certain students based on the their category and the count should be posted on their 'Master's ' row.  can someone please look  in below..  - last column is the expected answer. ...

ID	Item	Student	Category	Master_Ref	Sub Count
21	10	Master-1	Master		6
22	20	ST-2	SUB	10
23	30	ST-3	SUB	10
24	40	ST-4	SUB	10
25	50	ST-5	SUB	10
26	60	ST-5	SUB	10
27	70	ST-5	SUB	10
28	80	Master-2	Master		2
29	90	ST-7	SUB	80
30	100	ST-8	SUB	80

[sunny_talwar]

Is this needed in the script or front end?

[jt422605]

Hi Sunny - Its on front end - as selection would impact the counts as well 

[sunny_talwar]

If there another field which is common between the rows that needs to be counted and the row where they need to be displayed? For Example Is Master_Ref also available on Master row?

[jt422605]

Hi Sunny - 

Basically we need to count  on below conditions ... 

Category = 'SUB'  and Item =  Master_Ref  

then count all  item where Category = 'SUB'  and put the total on row Category = 'Master'

[sunny_talwar]

I understand that bit... but do you have a field which has the same value for SUB and MASTER rows?

[jt422605]

Spoiler

no sunny - 

[jt422605]

hi Sunny - I am thinking to create new field  OR amend master_ref   for sake of calculations...  if this helps - have a look .

D	Item	Student	Category	Master_Ref	Sub Count
21	10	Master-1	Master	10	6
22	20	ST-2	SUB	10
23	30	ST-3	SUB	10
24	40	ST-4	SUB	10
25	50	ST-5	SUB	10
26	60	ST-5	SUB	10
27	70	ST-5	SUB	10
28	80	Master-2	Master	80	2
29	90	ST-7	SUB	80
30	100	ST-8	SUB	80

[#9]

D	Item	Student	Category	Master_Ref	Sub Count
21	10	Master-1	Master	10	6
22	20	ST-2	SUB	10
23	30	ST-3	SUB	10
24	40	ST-4	SUB	10
25	50	ST-5	SUB	10
26	60	ST-5	SUB	10
27	70	ST-5	SUB	10
28	80	Master-2	Master	80	2
29	90	ST-7	SUB	80
30	100	ST-8	SUB	80

[sunny_talwar · Accepted solution]

Try this

If(Category = 'Master', Aggr(NODISTINCT Count(DISTINCT ID), Master_Ref)))

[jt422605]

Thanks genius  🙂 

I will further try to accommodate it Set Analysis.