Quicksight functions to derive net new users

Question

I am trying to find Net new users for a webpage for a time period. I could not find any option to exclude a set of data from a Quicksight filter.

The below is the SQL equivalent for what I am trying to achieve

```
SELECT DISTINCT login, webpage, logintime
FROM "website_usage_data"
WHERE logintime
BETWEEN date_trunc('month', date_add('month', -1, current_timestamp)) #Gets current month data
    AND date_trunc('month', current_timestamp) - INTERVAL '1' SECOND
    AND login NOT IN (
        SELECT login 
        FROM "website_usage_data"
        WHERE logintime 
        BETWEEN date_trunc('month', date_add('year', -5, current_timestamp)) 
            AND date_trunc('month', date_add('month', -1, current_timestamp))
        );
```
I think we might need to create a calculated field to indicate last month's logins and filter out logins prior to last month.

Sedat Salman · Answer

you can achieve this by creating a couple of calculated fields and then using them in your visualization

Create a calculated field for current month's data:

Field Name: current_month_data

Formula:

```
ifelse(
    month({logintime}) = month(now()) and year({logintime}) = year(now()),
    1,
    0
)
```

This calculated field will return 1 if the logintime is in the current month and 0 otherwise.

Create a calculated field for last month's data:

Field Name: last_month_data

Formula:

```
ifelse(
    month({logintime}) = month(dateDiff(now(), 1, 'MM')) and year({logintime}) = year(dateDiff(now(), 1, 'MM')),
    1,
    0
)
```

This calculated field will return 1 if the logintime is in the last month and 0 otherwise.

Create a calculated field to identify new users:

Field Name: new_users

Formula:

```
ifelse(
    sumOver({last_month_data}, [{login}], PRECEDING) = 0 and {current_month_data} = 1,
    1,
    0
)
```

This calculated field will return 1 if a user has not logged in during the last month but has logged in the current month, indicating that the user is new.

Finally, you can use the new_users calculated field in your visualization to show the net new users for the current month.

Quicksight functions to derive net new users

Relevant content