ARR Between Two Date Columns (Sub Start and End Dates)
I have three columns…
Subscription Start Date
Subscription End Date
Subscription ARR
Subscriptions are typically one year but not always. I want to chart ARR by month which is defined as the sum of all of the ARR for subscriptions active for each month.
X-Axis = Months ←- Not sure how to do this because if I pick Sub Start Date or End Date it will only sum the ARR for subs that end or start that month and not all of the months between Start and End
Y-Axis = Sum of all subscriptions active in that month.
0
Best Answer
-
You’d need to explode your data so that you have a record for each month. You can do this with magicETL and filter where day = 1 to only get the first of each month. Then using a formula tile on your data you’d need to calculate the first of the month with something like `Subscriotion Start Date` - INTERVAL (DAYOFMONTH(`Subscription Start Date`-1) DAY using the same formula tile just create a new column called Join and set the value to 1. On the date dimension dataset that Domo has from the Domo dimensions connector which you filtered on above fire day number 1 use another formula tile or tile create the same Join column with a value of 1. Then inner join both on join = join. Feed that into a filter with a formula to filter where the dimension month is between your start and end dates
then you should have each record for the month it was active. You can then use the month date in your visualization
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0
Answers
-
You’d need to explode your data so that you have a record for each month. You can do this with magicETL and filter where day = 1 to only get the first of each month. Then using a formula tile on your data you’d need to calculate the first of the month with something like `Subscriotion Start Date` - INTERVAL (DAYOFMONTH(`Subscription Start Date`-1) DAY using the same formula tile just create a new column called Join and set the value to 1. On the date dimension dataset that Domo has from the Domo dimensions connector which you filtered on above fire day number 1 use another formula tile or tile create the same Join column with a value of 1. Then inner join both on join = join. Feed that into a filter with a formula to filter where the dimension month is between your start and end dates
then you should have each record for the month it was active. You can then use the month date in your visualization
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 299 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 730 Beast Mode
- 55 App Studio
- 40 Variables
- 682 Automate
- 175 Apps
- 451 APIs & Domo Developer
- 46 Workflows
- 10 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive