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.
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
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 @GrantSmithJae 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
- 10.5K All Categories
- 3 Connect
- 913 Connectors
- 250 Workbench
- 458 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 476 Datasets
- 183 Visualize
- 249 Beast Mode
- 2.1K Charting
- 11 Variables
- 77 Cards, Dashboards, Stories
- 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
- 20 Manage
- 41 Governance & Security
- 168 Product Ideas
- 1.2K Ideas Exchange
- 9 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive