Month Over Month % Change of Total Number
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:
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?
Answers
-
@hunter_brown In your second formula, try changing the NULLIF functions to IFNULL.
0 -
-
Also the final solution should have the following arithmetic, not what I actually posted.
0 -
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.
0 -
@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)
0 -
@MichelleH The calculation validates but leaves me with an "issue occurred during processing" error.
0 -
@hunter_brown Do you have the beast mode filter from your original post applied to the card?
0 -
@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.
0 -
@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.0 -
@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.
1 -
@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.
1 -
It's irritating that NULL values tend to break math
1
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 294 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 707 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive