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.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive