Count Distinct

[luca_bellotti]

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.

[Saravanan_Desingh]

Can u try this?

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

[Kushal_Chawda]

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

[luca_bellotti]

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

[luca_bellotti]

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 .