Dynamic Annualized Number- Using Hard Coded Number Now for the Month
I have a beast mode that is currently using a hard coded number for the month (i.e 7 for July) and I want to make it dynamic to update each pervious month we close. Below is my current beast mode, which works but is not dynamic:
(SUM(case when `Period`>='2019-01-01' and `Period`<'2020-01-01' and `Ledger Type`='AA' and `Suppress Plan Month`='No' then `Amount` else 0 end)/7)*12
Best Answer
-
(SUM(case when `Period`>='2019-01-01' and `Period`<'2020-01-01' and `Ledger Type`='AA' and `Suppress Plan Month`='No' then `Amount` else 0 end)/MONTH(CURRENT_DATE()))*12
Try doing this MONTH(CURRENT_DATE())
Edit: Oh wait when you say previous month are you saying that this month being August (8) you want the report to be the month before aka /7 not 8, then in September you'd want /8.
IF so use MONTH(DATE_SUB(CURDATE(), 1 month))
**Make sure to like any users posts that helped you and accept the ones who solved your issue.**0
Answers
-
Just to clarify, are you saying the only number/date you're looking to make dynamic is the 7 in the divided by 7 section? Or you are also looking to make the '2019-01-01' and '2020-01-01' dynamic?
**Make sure to like any users posts that helped you and accept the ones who solved your issue.**0 -
@guitarhero23 I am only look to change the 7. Thanks for reviewing!
0 -
(SUM(case when `Period`>='2019-01-01' and `Period`<'2020-01-01' and `Ledger Type`='AA' and `Suppress Plan Month`='No' then `Amount` else 0 end)/MONTH(CURRENT_DATE()))*12
Try doing this MONTH(CURRENT_DATE())
Edit: Oh wait when you say previous month are you saying that this month being August (8) you want the report to be the month before aka /7 not 8, then in September you'd want /8.
IF so use MONTH(DATE_SUB(CURDATE(), 1 month))
**Make sure to like any users posts that helped you and accept the ones who solved your issue.**0 -
@guitarhero23 THANK YOU!
1 -
You got it pal!
**Make sure to like any users posts that helped you and accept the ones who solved your issue.**0
Categories
- All Categories
- 1.9K Product Ideas
- 1.9K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 310 Workbench
- 7 Cloud Amplifier
- 9 Federated
- 3K Transform
- 113 SQL DataFlows
- 650 Datasets
- 2.2K Magic ETL
- 4K Visualize
- 2.5K Charting
- 791 Beast Mode
- 78 App Studio
- 43 Variables
- 753 Automate
- 187 Apps
- 478 APIs & Domo Developer
- 71 Workflows
- 17 DomoAI
- 40 Predict
- 17 Jupyter Workspaces
- 23 R & Python Tiles
- 407 Distribute
- 118 Domo Everywhere
- 279 Scheduled Reports
- 10 Software Integrations
- 141 Manage
- 137 Governance & Security
- 8 Domo Community Gallery
- 47 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 113 Community Announcements
- 4.8K Archive