topic Re: Count Distinct in App Development

Count Distinct

luca_bellotti — Sat, 16 Nov 2024 01:53:25 GMT

Hi everyone,

I need to find the right formula to calculate the target of something. I loaded two tables joined by a field called ID.

TABLE A

ID	COUNTRY	REGION
1	USA	NORTH
2	USA	EAST
3	USA	NORTH
4	USA	SOUTH
5	USA	SOUTH
6	USA	EAST
7	USA	EAST
8	USA	NORTH
9	USA	EAST
10	USA	EAST

TABLE B

DATE	WEEK	ID	DEPARTMENT
01/01/2020	W01	1	A
01/01/2020	W01	1	B
01/01/2020	W01	1	C
02/01/2020	W01	2	A
02/01/2020	W01	3	A
02/01/2020	W01	7	A
02/01/2020	W01	7	B
08/01/2020	W02	4	A
09/01/2020	W02	10	A
10/01/2020	W02	1	C
10/01/2020	W02	1	B
15/01/2020	W03	6	A
17/01/2020	W03	9	A
17/01/2020	W03	1	A
17/01/2020	W03	3	A
17/01/2020	W03	5	A
23/01/2020	W04	7	B
23/01/2020	W04	2	B

What I have to find is for each region for each week the expected target of ID and the number of events of that ID.
Something like this:

	W01		W02		W03		W04
	EXPCTED	ACTUAL	EXPCTED	ACTUAL	EXPCTED	ACTUAL	EXPCTED	ACTUAL
NORTH	3	2	3	1	3	2	3	0
EAST	5	2	5	1	5	2	5	2
SOUTH	2	0	2	1	2	1	2	0

For the EXPECTED I tried this solution but it doesn't work
Alt(sum(aggr(Count(distinct ID),REGION)),'0')

For the ACTUAL i find this one and it's doin' pretty fine
Count(DISTINCT ID)

Thanks for the help.

Re: Count Distinct

Saravanan_Desingh — Wed, 15 Jul 2020 21:36:55 GMT

Can u try this?

Alt(sum(aggr(Count(distinct ID),REGION,WEEK)),0)

Re: Count Distinct

Kushal_Chawda — Wed, 15 Jul 2020 22:25:59 GMT

How you are getting expected count 5 for region EAST and W01.

EAST ID's are 2,6,7,9,10 out of which for W01 there is only 2,7,7 which should be 2 as distinct count?

Also actual expression count(distinct ID) not giving correct count as per your data

Re: Count Distinct

luca_bellotti — Thu, 16 Jul 2020 07:13:36 GMT

Yes, I've already tried and it doesn't work.
Thank you for the help anyway.

Re: Count Distinct

luca_bellotti — Thu, 16 Jul 2020 07:17:20 GMT

For the EXPECTED column In every week I need to find the max possible ID that are in the Table A. So basically every week I need to have the same numbers in the EXPECTED column .