Comparison between two identical tables in Quicksight

Question

I'm looking for some assistance. Currently I have two tables (left and right) that have identical columns and coming from one source of data. The only difference being the left table is the count of all active users from 1 Jan 24 and right being the count of all active users from 31 Jan 24. I am trying to find/identify users who have left the app by looking at the two tables. How do I query or create a calculated field if the user is seen present on the left table but is no longer showing on the right?

Hope this makes sense.

Have not tried attempting

Answer 1

Hoi ren, given your description of the tables, and since you haven't provided sample data, I assume your data includes a user_id and dates when they were "Active". If this is true then you can create a calculated expression which uses your date field to label each user_id based on last active date, something like:

ifelse(
max(date) <= "1 Jan 24", 
"old user",
ifelse(
    max(date) >= "1 Jan 24" AND max(date) < "31 Jan 24",
    "left", 
    "stayed"
    )
)

The first Ifelse labels all users NOT active since 1 Jan as "old users". The inner Ifelse labels all users with a max active date between your cut offs (1 to 31 Jan) as "left" and the rest as "stayed".

Then you can make a new table grouped by user_id, with this calc field as the value and see what status each user has. Filter the table to only show "left" users to get only those who have left the app. In my sample data I used the cut offs as Date > 5 and Date < 13 to get: