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
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_customer | Bank_Name | Age_Group | Governorate | Account_Number |
1110111 | Ahli | 40-60 | Alex | 101010 |
6660666 | Ahli | 40-60 | Alex | 808080 |
2220222 | QNB | 25-40 | Giza | 202020 |
2220222 | Ahli | 25-40 | Giza | 303030 |
3330333 | NBE | 25-40 | Cairo | 404040 |
3330333 | NBE | 25-40 | Cairo | 505050 |
3330333 | Ahli | 25-40 | Cairo | 606060 |
3330333 | Ahli | 25-40 | Cairo | 707070 |
this is the output of Alteryx workflow and input to Tableau
Count distinct Customer | Bank_Name | Age_Group | Governorate |
1 | Ahli | 25-40 | Cairo |
1 | NBE | 25-40 | Cairo |
1 | Ahli | 25-40 | Giza |
2 | Ahli | 40-60 | Alex |
1 | QNB | 25-40 | Giza |
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 Answer | Expected Answer | |
Cairo | 2 | 1 |
25-40 | 4 | 2 |
Is there any idea or solution to handle this issue.
Solved! Go to Solution.
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 |
1 | 25-40 | Cairo |
1 | 25-40 | Giza |
2 | 40-60 | Alex |
I hope this answers your question.
Best,
Yalmar
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 :)
Hi @Yalmar
Thanks for your reply.
Unfortunately, I need all the possible combinations of dimensions and a dynamic distinct count against it.
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
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.
Thanks Again @Yalmar,
We do that because of two things:
1-we need to go for a star schema model in order to boost the execution of query time
2-We work on a very large number of data (20 Million records) and we need to minimize a headache over Tableau