How to find the difference between 2 dates?

Options
Golfoholic
Golfoholic Member
in Magic ETL

My dataset aggregate value totals on a monthly basis. Each row represents an individual site and value for numerous metrics.

image.png


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.


image.png


Tagged:

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    Options

    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 <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Golfoholic
    Golfoholic Member
    Options

    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))

  • GrantSmith
    GrantSmith Coach
    Options

    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!**
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    Options

    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"

Categories