I'm working on visualizations that aim to show new customers within years. My database contains data from 2020 moving forward. I want to see new/unique customers that bought from us for the first team this year. If it is possibly want to compare it to 2021 too but struggle to filter that out or write a beast mode. Any recommendations? Was thinking of something like
WHEN 'firstorder' date (that's the place where I don't know how to write it) > '01.01.2022' THEN 'New Customer 2022'
WHEN 'firstorder' date <'12.31.2021' AND 'firstorder' date '01.01.2021' THEN 'New Customer 2022'
ELSE 'old customer'
Any ideas how to handle that?
(CASE WHEN YEAR(`firstorderdate`) = YEAR(TODAY()) THEN CONCAT('New Customer ',YEAR(`firstorderdate`)) WHEN YEAR(`firstorderdate` ) = YEAR(TODAY()) -1 THEN CONCAT('New Customer ',YEAR(`firstorderdate` )) ELSE 'OLD Customer' END )
Hi @zuchu - please try the above pasted code, this would be dynamic, so for current year, it would categorize the customers into New Customer 2022, New Customer 2021 and rest all as Old Customer.
When you step in 2023, your categories would become New Customer 2023, New Customer 2022 and rest all as Old Customer.
So it's, Dynamic and depends on your first order date.
Hope this works. For comparative charts, I would prefer using YOY charts when comparing with date ranges.
Aditya Jain'Happy to Help'0
Hi @Aditya_Jain , thanks for help mate! Appreciate your help, Unfortunately it doesn't work, should I create those edits in ETL or can I just create a beast mode within the analyzer?
I changed the name to my columns but I got an error saying: Calculation Error : This calculation is using a nonexistent function. Do you have any recommendations to solve that issue?
WHEN YEAR(`DocDate`) = YEAR(TODAY()) THEN CONCAT('New Customer ',YEAR(`DocDate`))
WHEN YEAR(`DocDate` ) = YEAR(TODAY()) -1 THEN CONCAT('New Customer ',YEAR(`DocDate` ))
ELSE 'OLD Customer'
I got the syntax to work, see below:
WHEN YEAR(`DocDate`) = YEAR(CURRENT_DATE())THEN CONCAT('New Customer ',YEAR(`DocDate`))
WHEN YEAR(`DocDate` ) = YEAR(CURRENT_DATE()) -1 THEN CONCAT('New Customer ',YEAR(`DocDate` ))
ELSE 'OLD Customer'
But it doesn't seem to resolving the issue. This way I just can see when does an order came, same as if I just applied a filter to show current year, I wanted to depict customers who showed up for the first time in a given year (ex. count of customer who did the first purchase in 2022). Any ideas how to handle that issue?
Up, anyone got any ideas? Would appreciate any input 🙏0
Do you have the customer's first order date as part of your dataset or do you need it calculated?**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0
@GrantSmith, unfortunately, I don't have first order for customer, I need to calculate that as well, I have customer name and date for each time they put an order0
Ok, you can utilize a Magic ETL and the Rank & Window tile to calculate the customer's order number. Make sure you sort on the order date and partition based on the customer identifier and calculate the row number. This will then get you a number for each customer's order with their first order having a value of 1. You can call it Order Count.
Then you can utilize the Order Count field in your visualization and filter for Order Count = 1 to only show the first orders for customers. Then you can also utilize a PoP chart to display the last year vs this year comparison.**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**2
@GrantSmith , thank you so much! It works!! Appreciate your help.0
- 10.5K All Categories
- 6 Connect
- 916 Connectors
- 250 Workbench
- 463 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 476 Datasets
- 189 Visualize
- 252 Beast Mode
- 2.1K Charting
- 11 Variables
- 78 Cards, Dashboards, Stories
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 246 Distribute
- 62 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 172 Product Ideas
- 1.2K Ideas Exchange
- 11 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive