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 Suite 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!