I am trying to create a Look to show the number of first time users by month based on their first payment_date (min) on a monthly basis. For example if I have user_id 123456 and he/she has made 3 payments on 02/07/2020, 02/28/2020 and 03/02/2020, I would count the user_id 123456 as 1 for the month of February (2020-02) as the first payment was made on 02/07/2020. I tried to create a calculated dimension “min($transactions.payment_date)” but I am not able to do so. Is it possible to achieve this using a Table Calculation instead of creating a measure of type date in LookML?
Thanks in advance! 😀
Below is my SQL which I am trying to match up the results of the Look with,
SQL code:
select
DATE_TRUNC(‘month’,first_payment_date) as month,
count(distinct user_id) as “Number_Of_First_Time_Users”
from
(select
user_id,
min(payment_date) as first_payment_date
from transactions
group by user_id
order by first_payment_date
)x
group by month
order by month
LinkedIn
Twitter
