Counting Dates on a date rage
In my business we sell a monthly service. A customer is either acivated or deactivated. I have a dataset that has 2 date columns (activation date and deactivation date). I can easily create 2 separate cards that show the number of activations and deactivations every month. The activation card would have the activation date (X axis) and count of activation date (Y axis). However, I want to combine those 2 cards so that I can see the number of activations every month compared to the number of deactivations every month. How do I do this with the Date Rage Field \ X axis being restricted to one of those 2 dates?
Best Answer
-
Hi Jferrara,
You'll need to do some sql on the backend (at least that's what we've done) that essentially adds a new line for every month that a customer is active with a count of 1. Then, when the customer is listed as deactivated, it returns a value of -1. This way, you'll have a new field of Customer Status, and your customer will be listed as "Active" every month until they are deactivated, where their status will then change to "Inactive". We used the mysql tool to write this code. Thanks!
1
Answers
-
Hi Jferrara,
You'll need to do some sql on the backend (at least that's what we've done) that essentially adds a new line for every month that a customer is active with a count of 1. Then, when the customer is listed as deactivated, it returns a value of -1. This way, you'll have a new field of Customer Status, and your customer will be listed as "Active" every month until they are deactivated, where their status will then change to "Inactive". We used the mysql tool to write this code. Thanks!
1
Categories
- 10.5K All Categories
- 6 Connect
- 916 Connectors
- 250 Workbench
- 460 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 476 Datasets
- 186 Visualize
- 250 Beast Mode
- 2.1K Charting
- 11 Variables
- 16 Automate
- 354 APIs & Domo Developer
- 88 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 245 Distribute
- 62 Domo Everywhere
- 242 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 170 Product Ideas
- 1.2K Ideas Exchange
- 10 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive