topic Re: Count based on multiple columns in App Development

Count based on multiple columns

jt422605 — Sat, 16 Nov 2024 04:12:06 GMT

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

Re: Count based on multiple columns

sunny_talwar — Thu, 31 Oct 2019 16:55:49 GMT

Is this needed in the script or front end?

Re: Count based on multiple columns

jt422605 — Thu, 31 Oct 2019 16:56:48 GMT

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

Re: Count based on multiple columns

sunny_talwar — Thu, 31 Oct 2019 17:02:21 GMT

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?

Re: Count based on multiple columns

jt422605 — Thu, 31 Oct 2019 17:05:33 GMT

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'

Re: Count based on multiple columns

sunny_talwar — Thu, 31 Oct 2019 17:16:15 GMT

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

Re: Count based on multiple columns

jt422605 — Thu, 31 Oct 2019 17:19:23 GMT

no sunny -

Re: Count based on multiple columns

jt422605 — Thu, 31 Oct 2019 17:22:17 GMT

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

Re: Count based on multiple columns

sunny_talwar — Thu, 31 Oct 2019 18:10:51 GMT

Try this

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

Re: Count based on multiple columns

jt422605 — Thu, 31 Oct 2019 19:14:39 GMT

Thanks genius 🙂

I will further try to accommodate it Set Analysis.

Re: Count based on multiple columns

SL2 — Fri, 18 Jun 2021 23:17:54 GMT

hi Sunny, I have a quite similar question but I don't know how to solve. Could you please help me with this?

https://community.qlik.com/t5/New-to-Qlik-Sense/find-records-with-same-dimensions/td-p/1816231

Much appreciated!