Month Over Month % Change of Total Number

hunter_brown
hunter_brown Member
in Beast Mode

I have revenue figures recorded at random intervals of days throughout months. It has not been updated since June, so I cannot use the automatic date filters in Domo without manually going in every month it gets updated and changing things around. I was able to get the most recently updated month and the prior month totals using the following BM filter:

image.png

The only drawback I can see doing this is that I cannot see in a single value card the % difference between the most recent month and the prior month.

I also tried to use the following source to create it, to no avail:

https://community-forums.domo.com/main/discussion/56630/month-over-month-change-formula

I also tried summing a new aggregated total column using a Group By function, which obviously didn't work as I was trying to take an aggregate of an aggregate. I finally tried using the same syntax as the filter for the date, which didn't work either.

image.png

Does anyone have any ideas?

Tagged:

Answers

  • MichelleH
    MichelleH Coach

    @hunter_brown In your second formula, try changing the NULLIF functions to IFNULL.

  • hunter_brown
    hunter_brown Member
    https://community-forums.domo.com/main/discussion/comment/75177#Comment_75177

    @MichelleH

    image.png

  • hunter_brown
    hunter_brown Member

    Also the final solution should have the following arithmetic, not what I actually posted.

    image.png

  • hunter_brown
    hunter_brown Member
    image.png

    This actually works, but the % difference is far too high. It gives -20%, when it should give closer to -3% or -4%. Almost as if it is taking the difference of only the last recorded days or something, which wouldn't make sense to me if the date filter is working.

  • MichelleH
    MichelleH Coach

    @hunter_brown It looks like you can actually simplify this calculation a bit. I've often found that errors can occur when nesting aggregates. Try something like this:

    (
  sum(case when LAST_DAY(`Posted dt.`) = LAST_DAY(CURRENT_DATE() - 60) then `Credit` - `Debit` else 0 end)
   - 
  sum(case when LAST_DAY(`Posted dt.`) = LAST_DAY(DATE_SUB(CURRENT_DATE() - 60), interval 1 month) then `Credit` - `Debit` else 0 end)
  )
  / 
  sum(case when LAST_DAY(`Posted dt.`) = LAST_DAY(DATE_SUB(CURRENT_DATE() - 60), interval 1 month) then `Credit` - `Debit` else 0 end)

  • hunter_brown
    hunter_brown Member
    https://community-forums.domo.com/main/discussion/comment/75185#Comment_75185

    @MichelleH The calculation validates but leaves me with an "issue occurred during processing" error.

  • MichelleH
    MichelleH Coach

    @hunter_brown Do you have the beast mode filter from your original post applied to the card?

  • hunter_brown
    hunter_brown Member
    edited August 2023

    @MichelleH No I removed it a while ago. There are no other filters applied to this specific card, and the ETL is linear with only a select and group by function.

    image.png

    image.png

  • hunter_brown
    hunter_brown Member
    edited August 2023

    @MichelleH There is something going on with summarizing credits and debits, as credits are all loading in properly but once I attempt to subtract debits I get lots of NaNs. I'm going to check this out and update.

    EDIT: Total revenue working now, going to try to import into BM. Had to IFNULL both credits and debits separately.

  • MichelleH
    MichelleH Coach

    @hunter_brown Are there any null values in the credits and debits columns? If one of them is null and the other is not then subtracting will return null, so you may need to replace the nulls with zeros in each individual column.

  • hunter_brown
    hunter_brown Member

    @MichelleH There are, that's what I was trying to use with the IFNULLS and NULLIFS. With your perspective I was able to get the correct calculations, pictured below.

    image.png

    image.png

    I also realize I could have done this NULL reassigning in an ETL for simplicity's sake, but I'm just happy it works.

  • trafalger
    trafalger Coach

    It's irritating that NULL values tend to break math

Categories