# Visualizing Difference that uses two sorting

**URL:** <https://community.amazonquick.com/t/visualizing-difference-that-uses-two-sorting/29501>\
**Category:** Q&A\
**Tags:** admin, quick-sight\
**Created:** [May 7, 2024, 4:45pm UTC](https://community.amazonquick.com/t/visualizing-difference-that-uses-two-sorting/29501 "2024-05-07T16:45:10Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![yampi](https://avatars.discourse-cdn.com/v4/letter/y/a9a28c/32.png) [@yampi](https://community.amazonquick.com/u/yampi)\
**Post date:** [May 7, 2024, 4:45pm UTC](https://community.amazonquick.com/t/visualizing-difference-that-uses-two-sorting/29501/1 "2024-05-07T16:45:10Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Xclipse](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/xclipse/32/20455_2.png) [@Xclipse](https://community.amazonquick.com/u/Xclipse)\
**Post date:** [May 8, 2024, 3:58am UTC](https://community.amazonquick.com/t/visualizing-difference-that-uses-two-sorting/29501/2 "2024-05-08T03:58:26Z")

</div>

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** :

```auto
ifelse(isNotNull({date_created}), 1, 0)

```

- **Deleted Count** :

```auto
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** :

```auto
truncDate("MM", {date_created})

```

- **Month of Deletion** (if you plan to also visualize deletions by the month they occur):

```auto
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.

```auto
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:

```auto
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)

---

<div class="post-metadata">

**Author:** ![Xclipse](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/xclipse/32/20455_2.png) [@Xclipse](https://community.amazonquick.com/u/Xclipse)\
**Post date:** [May 15, 2024, 12:37am UTC](https://community.amazonquick.com/t/visualizing-difference-that-uses-two-sorting/29501/3 "2024-05-15T00:37:39Z")

</div>