Amazon Quicksight Functions - SQL JOIN equivalent

Question

I am looking for a functionality similar to SQL LEFT JOIN in Amazon Quicksight using functions and filters.

I have a table with userID and LoginTime. Trying to calculate users who logged in for the first time for this month. ie Users for this month MINUS/LEFT JOIN users for previous months

Something along the lines of

not contains(logins, unique(logins between dateTrunc('YYYY', dateAdd('YYYY', -5, now())) and dateTrunc('MM', dateAdd('MM', -1, now()))))

Answer 1

Try to create a new calculated field called "FirstLoginThisMonth" to find the minimum login time for each user:

minOver({LoginTime}, [{userID}], PRE_AGG)

Then create another calculated field called "IsFirstLoginThisMonth" :

ifelse(dateDiffTrunc('MM', {FirstLoginThisMonth}, {LoginTime}) = 0, 'Yes', 'No')

It will compare the login time with the first login time of the user for the month and returns "Yes" if it's the first login this month, otherwise "No".

Then apply a filter using the "IsFirstLoginThisMonth" field with the filter condition:

"IsFirstLoginThisMonth" = 'Yes'