Counting unique orders each month
Hello
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?
Thanks!
Best Answer
-
So since you're wanting to show 'by day', a beastmode won't be the way to go on this one. A beastmode will only look at the rows falling into whatever grouping you define, in this case daily.
What you'll need is a new data transform that will return these users for you. Based on some of the fields you're using, a MySQL transform for this would look something like this:
SELECT DISTINCT `userid`, MIN(`submitted_at`) as 'First_Order_Date'
FROM datasetname
GROUP BY `userid` ,CONCAT(MONTH(`submitted_at`),YEAR(`submitted_at`))The above transform will return the first order date of the month for each user and the userid.
Let me know if you need any more detail or any additional help.
Sincerely,
ValiantSpur
**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.2
Answers
-
So since you're wanting to show 'by day', a beastmode won't be the way to go on this one. A beastmode will only look at the rows falling into whatever grouping you define, in this case daily.
What you'll need is a new data transform that will return these users for you. Based on some of the fields you're using, a MySQL transform for this would look something like this:
SELECT DISTINCT `userid`, MIN(`submitted_at`) as 'First_Order_Date'
FROM datasetname
GROUP BY `userid` ,CONCAT(MONTH(`submitted_at`),YEAR(`submitted_at`))The above transform will return the first order date of the month for each user and the userid.
Let me know if you need any more detail or any additional help.
Sincerely,
ValiantSpur
**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.2
Categories
- All Categories
- 1.4K Product Ideas
- 1.4K Ideas Exchange
- 1.4K Connect
- 1.2K Connectors
- 284 Workbench
- 4 Cloud Amplifier
- 4 Federated
- 2.9K Transform
- 88 SQL DataFlows
- 554 Datasets
- 2.2K Magic ETL
- 3.3K Visualize
- 2.3K Charting
- 562 Beast Mode
- 9 App Studio
- 27 Variables
- 577 Automate
- 140 Apps
- 414 APIs & Domo Developer
- 22 Workflows
- 1 DomoAI
- 28 Predict
- 12 Jupyter Workspaces
- 16 R & Python Tiles
- 350 Distribute
- 90 Domo Everywhere
- 258 Scheduled Reports
- 2 Software Integrations
- 91 Manage
- 88 Governance & Security
- 9 Product Release Questions
- Community Forums
- 42 Getting Started
- 28 Community Member Introductions
- 85 Community Announcements
- 4.8K Archive