How do I made a sum of a distinct count? For instance, if I want to sum up how many people were working on the 1st by the list of invoices, I would do a distinct count. What if I wanted to sum up all of those distinct counts for days 1-31?
Hi @Tblasz,
Distinct count works on a direct dimension. To perform distinct count by date, you would need to use distinctCountOver
syntax: distinctCountOver({Order ID},[{Order Date}],PRE_AGG)
details can be found here: distinctCountOver - Amazon QuickSight
Result:
Let us know if this resolves your issue.
Thanks,
Sri
Thank you for the reply.
How would I then sum up all the distinct counts?
With your example, lets say I wanted to sum distinct orders dates from Jan 4-7. It would be 12 but how do I write that in quicksight?
Hello @Tblasz , Please find the detailed instruction in the below snapshot for your reference.
Did my suggestion help you in resolving your query? If yes, would request you to mark the post as “Solution”. This will help the community to find guidance and answers to similar question. Thank you!
I’m sorry. I’m very new to this. I tried this formula but it didn’t work.
sumOver(sum(distinct_count({employee},{transaction_date}>'12/01/2022)))
I’m trying to get a distinct count of the employees each day and then add all those values up for the month of December.
You can’t put filter in the way that you have provided. You can select the visual and define the Filter criteria. Perhaps the below snapshot may help where you can define the filter. the syntax has to be like what I have shared in my expression.
1 Like
I took out the dat portion so I can filter it how you showed but the equation still doesn’t work.
sumOver(sum(distinct_count({employee},{transaction_date})))
Please reverify the syntax that you are putting. That {transaction_date} should be within a square bracket. Just like the below
Try to replace {Order ID} and {Order Date} respectively with employee and transaction_date
2 Likes
You are amazing! Thank you
2 Likes
