How to calculate distinct count of a column where i need to count only those values that are repeating based on time?

Question & Answer
,
1

Hi, I’m new to Quicksight and new to this community.
My dataset has over 10 million rows, which is data for 3 months. My requirement is that i have to count the distinct phone numbers in January but only the ones that also appeared in the previous month.
Below is the SQL code for achieving this. I’m not able to get the same value(The result of this SQL query) in Quicksight.

SELECT COUNT(DISTINCT phone) AS [Count] FROM sqltable
WHERE date BETWEEN '2023-01-01' AND '2023-01-31' AND phone IN (SELECT DISTINCT phone FROM sqltable
WHERE date BETWEEN '2022-12-01' AND '2022-12-31')
2

Non aggregated Quicksight calculated fields are looked at by the row level.

For your use case I would look at a lag that checks it’s last month as well.