The main difference, and the problem I’m currently running into is that my retention column is independent of time. I want to see how many people have reached visit 1, 2, 3, … But for my use case, time in weeks or months is not relevant than the number of visits a client has. Some clients have 1 visit every month, some have multiple in a week. I want to better reflect retention based on visits rather than breaking out weekly or monthly.
In an attempt to solve this, I created a runningTotal of visits for each client. I wanted to have that be my column row, but ran into an error that my custom aggregation field isn’t allowed as a dimension.
This is a quick approximation of what I’m trying to accomplish
This is a sample of the data set and columns that I’m working with. I put this into a new post since I ran into an restriction with uploading two images.
I was able to get (roughly) what I wanted by exporting this dataset with the calculated/aggregated columns and then reuploading back to QuickSight, so this means I can get it to work if I can get my source data into this format rather than relying on the calculation to be within QuickSight
I haven’t figured out the display of this to be in relation to original cohort (100%) rather than the percent difference from the previous column. I’m assuming that I’ll have to make another calculated field here, but I was able to export this into Google Sheets to finish the job.
I would say if you want to accomplish something like this with a pivot table in QuickSight you are definitely on the right track. Ideally, you would want to create groupings within the custom SQL through Joins where you create the retention groups and add rows to each group if they fulfill the requirement. Then, once you bring it into QuickSight, you wouldn’t run into the issue of needing to use a custom aggregation for the column values and would avoid the error.
Due to the nature of the runningSum aggregation and checking in a where clause if a certain row is within that grouping, this would likely be a scenario where you would want this custom SQL dataset specifically for this single visual. You are able to import many datasets into a dashboard, so I think this would be the best solution.
If you still need further assistance with this issue, please post a new topic in the community to ensure you are at the top of the priority list for a response from one of our QuickSight experts. Due to the nature of the community, 0 reply questions are often prioritized and may be why a response on your post was overlooked. You can also link this post in the new topic to ensure the admin user has access to the relevant information to give you the best insight towards your desired result. Thank you!
