How to find the difference between 2 dates?
My dataset aggregate value totals on a monthly basis. Each row represents an individual site and value for numerous metrics.
What I am trying to do would be how to find the change between 2 months like between Oct 1 , and Nov 1, as shown above for the values in the column to the right. But I am not sure how to write the appropriate beastmode.
The goal that I am trying to accomplish is to have a pivot table that I can take this column that would be showing the net difference of occupied area for the current and trailing 2 months.
Answers
-
You can utilize the LAG function to get the total from the previous month, like this:
LAG(SUM(`value`)) OVER (ORDER BY `activitydate`)
These window functions are not enabled by default. You will need to ask your CSM to "enable window functions in beast modes" in order to make use of this. Also, you can view this post and video to learn more about using this function.
https://dojo.domo.com/discussion/52679/domo-ideas-conference-beast-modes-rolling-averages#latest
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
NOTE: the description column has the names of many different metrics, and the value column has those values of the different metrics. To single out multiple individual metrics to display on the same visualization have had to use a beastmode such as: SUM((CASE when `Description` like '%Net%' then `Value` END))
0 -
Hi @user048760
Another option would be to define a custom date offset dimension for the current date, last month and 2 months ago. You can then conditionally calculate the rolling average over the past 3 months or look at the difference between months based on the offsets your define. I’ve done a write up about offset dimensions before: https://dojo.domo.com/discussion/comment/50540#Comment_50540
@jaeW_at_Onyx has done a video on this as well. https://www.youtube.com/watch?v=CDKNOmKClms&t=325s
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
thanks for the mention @GrantSmith
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 98 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 713 Beast Mode
- 50 App Studio
- 39 Variables
- 669 Automate
- 170 Apps
- 447 APIs & Domo Developer
- 45 Workflows
- 7 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 116 Manage
- 113 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 104 Community Announcements
- 4.8K Archive