Month Over Month % Change of Total Number

hunter_brown

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:

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.

Does anyone have any ideas?

MichelleH

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

hunter_brown

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

@MichelleH

hunter_brown

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

hunter_brown

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

@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

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

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

hunter_brown

@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.

hunter_brown

@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

@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

@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.

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

It's irritating that NULL values tend to break math