How to find the difference between 2 dates?

Golfoholic

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.

MarkSnodgrass

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

Golfoholic

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

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

jaeW_at_Onyx

thanks for the mention @GrantSmith