Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Count distinct not additive field

Ahmed_Massoud90
5 - Atom

Hello Alteryx Team,

 

I'm a beginner in Alteryx designer and I have a question with Count distinct tool in the designer:

the case is I need to create a star schema model attached below Star Schema Model.jpg 

 the count of service is straightforward but the count of customers must be distinct count and here is the issue.

Count distinct is correct on the lowest granularity level ( including all the dimension list above the distinct ) but when I 'm trying to visualize the count distinct against any higher part of granularity the count distinct is not correct because the count distinct result aggregated.

Let's look at the following example :

 

This is the original data 

 

Pkey_customerBank_NameAge_GroupGovernorateAccount_Number
1110111Ahli40-60Alex101010
6660666Ahli40-60Alex808080
2220222QNB25-40Giza202020
2220222Ahli25-40Giza303030
3330333NBE25-40Cairo404040
3330333NBE25-40Cairo505050
3330333Ahli25-40Cairo606060
3330333Ahli25-40Cairo707070

 

this is the output of Alteryx workflow and input to Tableau

Count distinct CustomerBank_NameAge_GroupGovernorate
1Ahli25-40Cairo
1NBE25-40Cairo
1Ahli25-40Giza
2Ahli40-60Alex
1QNB25-40Giza

 

When trying to visualize over Tableau the count distinct number of customers  against the governorate only or Age Group dimension only I aggregate and give a wrong number Like:

 Got AnswerExpected Answer
Cairo21
25-4042

 

Is there any idea or solution to handle this issue.

6 REPLIES 6
yalmar_m
11 - Bolide

Hi @Ahmed_Massoud90,

 

I think your problem is related to the group by on 'Bank_Name' in Alteryx.

If you dicard this, you'll get the following output.

 

CountDistinct_Pkey_customer Age_Group Governorate


25-40 Cairo
1  

25-40Giza
40-60 Alex

 

I hope this answers your question.

 

Best,

Yalmar

SamDesk
11 - Bolide

Hello @Ahmed_Massoud90,

 

In addition to @yalmar_m's response above, if your need for the count distinct is only within Tableau, then you can simply hold right-click and drag any dimension in Tableau onto the sheet and select an option of count distinct for that selected dimension. This should hopefully solve your problem.

 

I think the misunderstanding may be that the count distinct function from Alteryx does not create a dynamic distinct count field, unlike in Tableau where distinct counts update relative to the filters and dimensions present in the sheet. Alteryx is creating a fixed distinct count at the granularity specified and is then open to aggregation by Tableau.

 

Sam :)

Ahmed_Massoud90
5 - Atom

Hi @Yalmar 

 

Thanks for your reply.

Unfortunately, I need all the possible combinations of dimensions and a dynamic distinct count against it.

yalmar_m
11 - Bolide

Hi @Ahmed_Massoud90

 

Why do you want to prepare and aggregate in Alteryx?

If you want to have all possible combinations in Tableau, it might be better to use your 'original data' as input in Tableau.


Best,

Yalmar

Ahmed_Massoud90
5 - Atom

Hello @samDesk,

 

Thanks for your help. That's already we figured out and we were asking if there's a workaround for a dynamic distinct count function.

Ahmed_Massoud90
5 - Atom

Thanks Again @Yalmar

Labels