Issues in calculation a distint count condition

Question & Answer
, ,
1

ifelse(
event_name = ‘TRANSACTION_SUBMITTED’,
ifelse(
instance_id = ‘1’, 85,
ifelse(instance_id = ‘2’, 179,
ifelse(instance_id = ‘3’, 449,
ifelse(instance_id = ‘4’,
ifelse(parseInt(year) >= 2023 and parseInt(month) >= 8 and parseInt(day) >= 3, 500, 100),
ifelse(instance_id = ‘5’, 0,
ifelse(instance_id = ‘6’,
ifelse(parseInt(year) >= 2023 and parseInt(month) >= 8 and parseInt(day) >= 3, 500, 100),
ifelse(instance_id = ‘7’,
ifelse(parseInt(year) >= 2023 and parseInt(month) >= 8 and parseInt(day) >= 3, 500, 100),
ifelse(instance_id = ‘8’,
ifelse(parseInt(year) >= 2023 and parseInt(month) >= 8 and parseInt(day) >= 3, 500, 100),
ifelse(instance_id = ‘9’, 349,
ifelse(instance_id = ‘10’, 349,
ifelse(instance_id = ‘11’, 499,
ifelse(instance_id = ‘12’, 800,
ifelse(instance_id = ‘13’, 1700,
ifelse(instance_id = ‘14’,
ifelse(countDistinct(transaction_id) > 1667, 25, 35),
0)))))))))))))),
0
)

There query isn’t working. Can anyone help?

2

Hi @kchalla.05
What is the error you get?
Can you also please try the distinctCountOver window function if the error is related to aggregation?

Regards
Vetri

3

Hi @kchalla.05,

countDistinct should be distinct_count

distinct_count - Amazon QuickSight

but as @n_vetri said, what is the error?

BR

4

if you go to the error message, you will see this message “Mismatched aggregation. Custom aggregations can’t contain both aggregate “NESTED_COUNT” and non-aggregated fields “NESTED_COUNT(DISTINCT “product”,)”, in any combination.”

you can fix by using window function as suggested by @n_vetri distinctCountOver - Amazon QuickSight

You may refer to below highlighted line in the screenshot
image