Announcements
cancel
Showing results for
Did you mean:
Contributor III

## Count Distinct

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.

Labels (2)

• ### function

4 Replies

Can u try this?

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

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

Contributor III
Author

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

Contributor III
Author

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 .

Tags
Community Browser