Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
luca_bellotti
Contributor III
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

IDCOUNTRYREGION
1USANORTH
2USAEAST
3USANORTH
4USASOUTH
5USASOUTH
6USAEAST
7USAEAST
8USANORTH
9USAEAST
10USAEAST

 

TABLE B

DATEWEEKIDDEPARTMENT
01/01/2020W011A
01/01/2020W011B
01/01/2020W011C
02/01/2020W012A
02/01/2020W013A
02/01/2020W017A
02/01/2020W017B
08/01/2020W024A
09/01/2020W0210A
10/01/2020W021C
10/01/2020W021B
15/01/2020W036A
17/01/2020W039A
17/01/2020W031A
17/01/2020W033A
17/01/2020W035A
23/01/2020W047B
23/01/2020W042B

 

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 
 EXPCTEDACTUALEXPCTEDACTUALEXPCTEDACTUALEXPCTEDACTUAL
NORTH32313230
EAST52515252
SOUTH20212120

 

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)
4 Replies
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
Contributor III
Contributor III
Author

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

luca_bellotti
Contributor III
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 .