Visualizing Difference that uses two sorting

Q&A
,
1

Hi everyone!

I have a dataset with a user ID, the date it was created and the date it was deleted. I need to calculate the cumulative of users per month. So if the first month two users were created and the second month two more were created but one was deleted, then the first month I have two users and then three users.

I tried difference but since I am sorting by two measures I haven´t been able to visualize it. It would be a running sum of created users minus the deleted users in that month.

Greatly appreciate the help.

2

Hi @yampi, welcome to the Quick Sight community, to calculate the cumulative count of users per month in Amazon Quick Sight, considering both the creation and deletion of users, you will need to follow a few steps to set up your data and visualization. Here’s a step-by-step guide:

  1. Prepare Your Data: Ensure your dataset has columns for user_id, date_created, and date_deleted. If date_deleted is NULL for users that haven’t been deleted, that’s fine.
  2. Create Calculated Fields: You’ll need to create some calculated fields in Quick Sight to handle the counts of created and deleted users.
  • Created Count:
ifelse(isNotNull({date_created}), 1, 0)
  • Deleted Count :
ifelse(isNotNull({date_deleted}), 1, 0)
  1. Handle Monthly Aggregation: Aggregate the created and deleted counts by month. You can use the Truncated function to group dates by month:
  • Month of Creation :
truncDate("MM", {date_created})
  • Month of Deletion (if you plan to also visualize deletions by the month they occur):
truncDate("MM", {date_deleted})
  1. Create a Running Total Field: You need a running total that accounts for both creations and deletions. First, ensure you can calculate a net change per month, then compute a running total.
  • Monthly Net Change : Create a new field that subtracts the total deletions from total creations for each month.
sum({Created Count}) - sum({Deleted Count})

** Cumulative Users* : Use Quick Sight’s running total calculation to sum the Monthly Net Change over time. You can use the runningSum function, ensuring to order by the month:

runningSum({Monthly Net Change}, [{Month of Creation}], PRE_AGG)
  1. Visualize the Data:
  • Create a new visual in Quick Sight.
  • Choose a line chart or an area chart for a clear representation of the cumulative totals.
  • Set the X-axis to the Month of Creation or whichever date field represents the timeline.
  • Set the Y-axis to the Cumulative Users calculated field.
  1. Adjust Filters and Controls: If necessary, add filters or control widgets to allow dynamic interaction with the visualization, such as selecting specific date ranges or user segments.

By setting up your dataset and visuals with these calculated fields, you should be able to effectively track and visualize the cumulative count of active users over time, accounting for both new registrations and deletions.

We hope this solution worked for you. Let us know if this is resolved. And if it is, please help the community by marking this answer as a “Solution.” (click the check box under the reply)