Difference of values between 2 months

MayaU_01
MayaU_01 Member
in Beast Mode

I have a value column named 'Value' and a month column named 'Month'. I want to create a beast mode to calculate the difference between values of the last two months.


So if we have two months in the dataset - Nov 23 and Oct 23. I will want to calculate the difference in value between both months. It is a month-to-month difference. If there is no new month in the dataset like if we are in the Jan 24 month but in the dataset there is no data for Jan 24 then it should print 0.

Tagged:

Best Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    If you have one value per month you can utilize a LAG window function. I've written up about these here:

    https://community-forums.domo.com/main/discussion/52679/domo-ideas-conference-beast-modes-rolling-averages

    Alternatively you can restructure your data so that you have a period type (Current, Last Month) and a specific value for your months then utilize some beast modes to calculate the difference. Here's another write up on this method:

    https://community-forums.domo.com/main/discussion/53481/a-more-flexible-way-to-do-period-over-period-comparisons

    A third option is to utilize an ETL and join the data to itself based on a month prior using a formula tile:

    `Month` - INTERVAL 1 MONTH

    Then join it back onto itself based on the one month prior and the current month and rename your one month prior values as new columns

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • nmizzell
    nmizzell Contributor
    Answer ✓

    Let's assume that the "Month" column has the first date of the month referenced.

    The Beast Mode you will want to use is the following:

    sum(case when `Month` = date_sub(`Month`, interval 1 month) then `Value` end) - sum(case when `Month` = date_sub(`Month`, interval 0 month) then `Value` end)

    if you want to return 0 instead of null for empty values, simply wrap your function in a case expression:

    example: case when (function) is null then 0 else (function) end

    case when (sum(case when `Month` = date_sub(`Month`, interval 1 month) then `Value` end) - sum(case when `Month` = date_sub(`Month`, interval 0 month) then `Value` end)) is null then 0 else (sum(case when `Month` = date_sub(`Month`, interval 1 month) then `Value` end) - sum(case when `Month` = date_sub(`Month`, interval 0 month) then `Value` end)) end

    If you want to return current month - prior month instead of prior month - current month, just flip the order of the subtraction in the function:

    (-1 * sum(case when `Month` = date_sub(`Month`, interval 1 month) then `Value` end) )+ sum(case when `Month` = date_sub(`Month`, interval 0 month) then `Value` end)

    Full version using flipped subtraction order:

    case when ((-1 * sum(case when `Month` = date_sub(`Month`, interval 1 month) then `Value` end) )+ sum(case when `Month` = date_sub(`Month`, interval 0 month) then `Value` end)) is null then 0 else (f(-1 * sum(case when `Month` = date_sub(`Month`, interval 1 month) then `Value` end) )+ sum(case when `Month` = date_sub(`Month`, interval 0 month) then `Value` end)) end

    I have never tried to use interval 0 month with date_sub, so there's a chance that it might not work. Nothing ventured, nothing gained!

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    If you have one value per month you can utilize a LAG window function. I've written up about these here:

    https://community-forums.domo.com/main/discussion/52679/domo-ideas-conference-beast-modes-rolling-averages

    Alternatively you can restructure your data so that you have a period type (Current, Last Month) and a specific value for your months then utilize some beast modes to calculate the difference. Here's another write up on this method:

    https://community-forums.domo.com/main/discussion/53481/a-more-flexible-way-to-do-period-over-period-comparisons

    A third option is to utilize an ETL and join the data to itself based on a month prior using a formula tile:

    `Month` - INTERVAL 1 MONTH

    Then join it back onto itself based on the one month prior and the current month and rename your one month prior values as new columns

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • nmizzell
    nmizzell Contributor
    Answer ✓

    Let's assume that the "Month" column has the first date of the month referenced.

    The Beast Mode you will want to use is the following:

    sum(case when `Month` = date_sub(`Month`, interval 1 month) then `Value` end) - sum(case when `Month` = date_sub(`Month`, interval 0 month) then `Value` end)

    if you want to return 0 instead of null for empty values, simply wrap your function in a case expression:

    example: case when (function) is null then 0 else (function) end

    case when (sum(case when `Month` = date_sub(`Month`, interval 1 month) then `Value` end) - sum(case when `Month` = date_sub(`Month`, interval 0 month) then `Value` end)) is null then 0 else (sum(case when `Month` = date_sub(`Month`, interval 1 month) then `Value` end) - sum(case when `Month` = date_sub(`Month`, interval 0 month) then `Value` end)) end

    If you want to return current month - prior month instead of prior month - current month, just flip the order of the subtraction in the function:

    (-1 * sum(case when `Month` = date_sub(`Month`, interval 1 month) then `Value` end) )+ sum(case when `Month` = date_sub(`Month`, interval 0 month) then `Value` end)

    Full version using flipped subtraction order:

    case when ((-1 * sum(case when `Month` = date_sub(`Month`, interval 1 month) then `Value` end) )+ sum(case when `Month` = date_sub(`Month`, interval 0 month) then `Value` end)) is null then 0 else (f(-1 * sum(case when `Month` = date_sub(`Month`, interval 1 month) then `Value` end) )+ sum(case when `Month` = date_sub(`Month`, interval 0 month) then `Value` end)) end

    I have never tried to use interval 0 month with date_sub, so there's a chance that it might not work. Nothing ventured, nothing gained!

Categories