I need to calculate a running count of the unique users. These are the meanings of each column.
A: Transaction Day
B: Transaction ID
C: User (or User ID)
D: Count of Unique User in the Transaction Day
E: Running (cumulative) counts of Unique User based on the runnningSum formula
F: Actual (expected) results
Since I need a cumulative count at sequential days, I used runningSum as shown at the bottom of the screenshot. But it is not the right answer. How do I get the result in Column F?
Hello @SC_CA, welcome to the QuickSight Community!
Okay, I ran some testing locally on my end and I don’t believe you will get any closer only working through QuickSight calculated fields. Do you have the ability to update your dataset through custom SQL? The exact SQL will differ slightly, but if you are able to create a new column that is a distinct count of the Users partitioned by day, then I believe utilizing that in your running sum could work. The only thing you would have to consider is that it would return the total distinct count for each day on every row, so you would need to use something like average or min on that field. I think that is likely the best way to try and achieve this.
Thanks, Dylan. I guess there was no direct way to handle this in QS. We will probably implement it at the table level as you suggested.
Hello @SC_CA, that definitely seems like the best option. If you run into any issues displaying this after making the changes in your table, please post a new question in the community and we can assist. That will ensure you are at the top of the priority list for a response from one of or QuickSight experts. Thank you and good luck!