I am trying to find Net new users for a webpage for a time period.
ie: Users who started using the website for the first time this month (ie they never logged in prior to this month) this can be calculated as *All users for last month MINUS users who logged in at least once in till last month
The below is the SQL equivalent for what I am trying to achieve
SELECT DISTINCT login, webpage, 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 (
BETWEEN date_trunc('month', date_add('year', -5, current_timestamp))
AND date_trunc('month', date_add('month', -1, current_timestamp))
I could not find any option to exclude a set of data using filters.
Hi @balumath - Welcome to AWS QuickSight community and thanks for posting the question. Couple of suggestions -
- Is your data source is relational database? If yes, you can test your sql in DB and then use custom sql in QuickSight and do the reporting.
- If you are trying to filter the users for the last month minus all users before that ( you do not need to check whether user is login or not), then try to use left join rather minus as minus is a costly operation.
- if you are looking for a solution to bring all data to QuickSight data set preparation, you can create some count w.r.t login and put some filter and build the report on the base data set.
Regards - Sanjeeb
I am trying to bring all data to Quicksight and and prepare the data
- I could do the SQL using Athena, but I am loosing the ability to calculate month over month calculations because NetNew users will be a new Dataset
Is there an example of doing Left joins using functions and filters as a filter typically doesn’t exclude the “intersection”