Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Yes, I've already tried and it doesn't work.
Thank you for the help anyway.
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 .