Bar chart with distinct_count value - sort x-axis using distinct_count value

Q&A
, , , ,
1
  1. QuickSight has dataset having fields - product, country, membercode, year, brand, invoicedate
  2. Create a calculated field, rankProduct = rank([distinct_count(membercode) DESC], [brand, year]) —> to filter Top N product
  3. Create a Vertical bar chart with x-axis=product, value=distinct_count(membercode), group/color=year, small multiples=brand

Vertical bar chart got error “Adding totals to distinct count aggregations is currently unsupported.” by either of these 2 actions:
a) x-axis; sort by membercode
b) filter using rankProduct less than or equal paramTopN

Expected result is: Top N products for distinct count of membercode in descending order

image
image826×645 23.2 KB

2

Hi @lbl,

Hope everything is well! I believe the reason for this (and let me know if this is not the case for you) is because the rank function is looking for numbers, and even though the membercode field is displayed as a number, it might be recognized on Quick as a string value.

If this is the case for you, I would try to find a way to either change how the field is recognized on the dataset level, or use different functions (such as parseInt) to make it be recognized as a number.

Let me know if this helps!

3

@WLS-Luis It is not the case you mentioned. If I used count(membercode) instead of distinct_count(membercode), there is no error. If I use Pivot table, ROWS = year, brand, product; VALUES = membercode (Count distinct), this also works without error.

It is purely QS not supporting sorting by distinct_count in Bar Chart. Any reason?

Can this be a new feature request?

4

Hi @lbl,

Thanks for further explaining this, and this does indeed seem to be the case, especially due to the error being raised on your end. I will make sure to mark this as a Feature Request so that someone from the AWS Team can address this, and I will leave this post open for a while in case we can find a workaround in the meantime!

5

Hi @lbl

It’s been a while since we last heard from you. If you have any further questions, please let us know how we can assist you.

If we don’t hear back within the next 3 business days, we’ll proceed with close/archive this topic.

Thank you!

7

Hi @lbl

Since we have not heard back from you, I’ll go ahead and close/archive this topic. However, if you have any additional questions, feel free to create a new topic in the community and link this discussion for relevant information.

Thank you!