Membership Length

Hi new user here,
I have a question about getting the membership length of our customers and I am doing this as a beast mode for a card:
- I need to find the start date of membership
- I need to find the end date of membership
- Finally the avg time they have been a member
I used the following case statement to try and find the start and end dates:
- StartDate - Beast mode calculation
- CASE WHEN
countasUWCsignup
= 1
ANDcustomer type
= 'UWC'
ThenDate
- EndDate - Beast mode calculation
- CASE WHEN
countasUWCsignup
= 1
ANDcustomer type
= 'UWC'
ThenDate
- UWC is the membership type but I am not getting the correct start or end dates
If I do a filter on the card for using Customer type nd countasUWCsignup =1 I get the correct start date.
Thank you
Best Answers
-
Is there a unique ID for each customer? Is there a field that indicates a cancellation? If there is a single date column that would mean that signup and cancellation dates would be on separate rows in which case you would need to do what @MarkSnodgrass pointed out. What you've shared is not enough information to do what you are hoping.
If I solved your problem, please select "yes" above
0 -
thank you
0
Answers
-
Without seeing your data or understanding its fields it is tough to know which fields you ought to use, however the calculation is pretty straightforward once you do:
CASE WHEN `statusOfCustomer` = 'Active' THEN DATEDIFF(CURDATE(),`enrollmentDate`)
WHEN `statusOfCustomer` = 'Cancelled' THEN DATEDIFF(`cancelledDate`,`enrollmentDate`) ENDThis beastmode would give you the difference in days between those dates to calculate your average time they have been a member.
If I solved your problem, please select "yes" above
0 -
If you could provide a few sample rows of data with sensitive information obfuscated the community could likely help you figure it out.
If I solved your problem, please select "yes" above
0 -
If your start and end dates are in the same row, then it is pretty straightforward as @ColemenWilson points out. If your start and end dates are in the same column, but different rows, then you will need to use Magic ETL to get it consolidated so that the start and end dates are in the same row and separate columns.
**Check out my Domo Tips & Tricks Videos
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
Hi Colemen and Mark,
Here is some sample data:
You are either a member on a certain date until you expire and that is pretty much it is setup. Hope this helps.
Thanks
0 -
Is there a unique ID for each customer? Is there a field that indicates a cancellation? If there is a single date column that would mean that signup and cancellation dates would be on separate rows in which case you would need to do what @MarkSnodgrass pointed out. What you've shared is not enough information to do what you are hoping.
If I solved your problem, please select "yes" above
0 -
thank you
0
Categories
- All Categories
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 311 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3.8K Transform
- 659 Datasets
- 116 SQL DataFlows
- 2.2K Magic ETL
- 816 Beast Mode
- 3.3K Visualize
- 2.5K Charting
- 82 App Studio
- 45 Variables
- 776 Automate
- 190 Apps
- 481 APIs & Domo Developer
- 82 Workflows
- 23 Code Engine
- 40 AI and Machine Learning
- 20 AI Chat
- 1 AI Playground
- 1 AI Projects and Models
- 18 Jupyter Workspaces
- 410 Distribute
- 120 Domo Everywhere
- 280 Scheduled Reports
- 10 Software Integrations
- 144 Manage
- 140 Governance & Security
- 8 Domo Community Gallery
- 48 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 114 Community Announcements
- 4.8K Archive