Hi,
I am trying to create an insight with:
X axis : date column
VALUE: customer_id string column
color: category_type string column
The insight is created when the aggregation is set to ‘count’ however when I select ‘count distinct’ I get an error(Period to date: Error occurred during arithmetic operations such as…)
Why I am getting this error? Is there any alternative to calculate: YTD comparison of distinct count(customer_ids?)
Hello @jgrueso, welcome to the QuickSight community!
I am curious, rather than changing the aggregation in the field well, what if you replace your customer_id string column with a calculated field like this:
distinct_count({customer_id})
Then maybe that would fix the issue? Also, are you trying to compare YTD of distinct customer IDs or are you just wanting to show the number of distinct customer IDs for this year so far? It may be worth switching to a Total Aggregation function in the insight if the 2nd option meets your use case.
Hi @DylanM, this doesn’t solve the issue. Same error as I get from the field well. I am not really sure what the problem is since the only difference is the ‘distinct’, this means, removing the duplicate values.
The comparison is YTD
Hello @jgrueso, are you able to show an anonymized version of how your insight narrative is being built out? I am curious if we can find a work-around with a different narrative function based on your use-case.
Another option would be to set up another dataset with custom SQL that only brings in distinct customer_ids so you only return a single row for each. Maybe that would be useful on some of your other visuals as well.
Hello @jgrueso, I wanted to check in on your progress since we have not heard back from you. Do you still need assistance with this issue? If so, please respond with the information I requested in my last reply so I can try to help. If we do not heard back from you in 3 days, I will archive this topic. Thank you!
Hi Dylan,
Unfortunately I could not share an anonymized version. However I changed the approach and I managed to resolve the issue from a different point of view.
Thank you for the support.
Hello @jgrueso, thank you for following up! I wanted to make sure you were able to get your desired solution.