Counting unique orders each month
I'm trying to build a beastmode calculation that will only count users ordering for the first time in any given month.
My end game is to be able to have a vertical chart showing by day users ordering for the first time that month, however using a count(distinct) beast mode command will only tell me unique orders for the time period specified in the card filter.
My beastmode calc currently looks like this:
COUNT(DISTINCT case when `submitted_at` >= STR_TO_DATE(CONCAT(YEAR(`submitted_at`),'/',MONTH(`submitted_at`),'/1'),'%Y,%m,%d') and `submitted_at`<= CURDATE() then `user_id` else '0' end), however this is still returning the same values on a day by day basis as a simple count(distinct) command.
Can anyone offer any insight as to where I'm falling down in my command?