Calculating a final value from two selected values that exist in the same column?
I have two columns—one column has revenue, the other column states which quarter the revenue was collected in.
Is there a way that allows a user to select a quarter to calculate the difference in revenue between the selected quarter and another selected quarter.
So, I am basically looking for a solution that would enable the end user to select any two quarters that would then cause domo to calculate the difference in revenue between the selected quarters.
Thanks in advance, and let me know if Domo doesn't support a calculation of this sort.
Best Answers

You could potentially use two variables and a beast mode to calculate the difference between your two values however there isn't a way to automatically populate these values in the variable so you'd need to manually enter them.
Alternatively you can do a giant cross product of your dataset such that you have the data duplicated for each quarter. There would be a Quarter 1 filter and a Quarter 2 filter which would then allow you to use filter cards to get the records with just those quarters but will drastically increase your dataset.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**2 
A less elegant way which I had to use in the past, that might come in handy in your case (supposing the quarter is an actual value and not dates that you're having Domo aggregate to a specific quarter, in which case you'll need to deal with it).
So my sample dataset has 2 columns as you mentioned, Revenue and Collected Quarter, I even have multiple entries on each quarter to show that you can do the aggregation at the Beast Mode level:
Just for comparison we can get the total revenue per quarter
From here we trick the system and use a Single value card, with some specific settings, like making the quarters a quick filter, so users can select which ones to compare, limiting the results to show a single line, and sorting on the Collected quarter descending (so we always get the difference from the latest quarter to the newest, this will depend on your needs)
So your card will look something like:
Here the Beast Mode for Difference is defined as:
CASE WHEN (SUM(COUNT(DISTINCT Collected Q)) FIXED ()) != 2 THEN 'Select exactly 2 quarters' ELSE SUM(Revenue)  LAG(SUM(Revenue),1) OVER (ORDER BY Collected Q ASC) END
The first case statement makes sure that we're only showing 2 quarters and that if you have more than or less than that you get a nice error message that asks people to select exactly 2. Then once we know we only have 2 quarters we get the difference between the SUM for the current row minus the SUM of the previous one (obtained using the lag function).
Tada!!! you have a way to do what you wanted without additional ETLs or datasets, just in a very weird and odd way 🤣
1
Answers

You could potentially use two variables and a beast mode to calculate the difference between your two values however there isn't a way to automatically populate these values in the variable so you'd need to manually enter them.
Alternatively you can do a giant cross product of your dataset such that you have the data duplicated for each quarter. There would be a Quarter 1 filter and a Quarter 2 filter which would then allow you to use filter cards to get the records with just those quarters but will drastically increase your dataset.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**2 
Cool! Can I duplicate the columns in Magic ETL?
0 
A less elegant way which I had to use in the past, that might come in handy in your case (supposing the quarter is an actual value and not dates that you're having Domo aggregate to a specific quarter, in which case you'll need to deal with it).
So my sample dataset has 2 columns as you mentioned, Revenue and Collected Quarter, I even have multiple entries on each quarter to show that you can do the aggregation at the Beast Mode level:
Just for comparison we can get the total revenue per quarter
From here we trick the system and use a Single value card, with some specific settings, like making the quarters a quick filter, so users can select which ones to compare, limiting the results to show a single line, and sorting on the Collected quarter descending (so we always get the difference from the latest quarter to the newest, this will depend on your needs)
So your card will look something like:
Here the Beast Mode for Difference is defined as:
CASE WHEN (SUM(COUNT(DISTINCT Collected Q)) FIXED ()) != 2 THEN 'Select exactly 2 quarters' ELSE SUM(Revenue)  LAG(SUM(Revenue),1) OVER (ORDER BY Collected Q ASC) END
The first case statement makes sure that we're only showing 2 quarters and that if you have more than or less than that you get a nice error message that asks people to select exactly 2. Then once we know we only have 2 quarters we get the difference between the SUM for the current row minus the SUM of the previous one (obtained using the lag function).
Tada!!! you have a way to do what you wanted without additional ETLs or datasets, just in a very weird and odd way 🤣
1
Categories
 All Categories
 1.1K Product Ideas
 1.1K Ideas Exchange
 1.2K Connect
 969 Connectors
 257 Workbench
 Cloud Amplifier
 1 Federated
 2.4K Transform
 76 SQL DataFlows
 501 Datasets
 1.8K Magic ETL
 2.7K Visualize
 2.2K Charting
 377 Beast Mode
 20 Variables
 486 Automate
 104 Apps
 378 APIs & Domo Developer
 6 Workflows
 22 Predict
 6 Jupyter Workspaces
 16 R & Python Tiles
 317 Distribute
 65 Domo Everywhere
 252 Scheduled Reports
 59 Manage
 59 Governance & Security
 1 Product Release Questions
 5K Community Forums
 37 Getting Started
 23 Community Member Introductions
 64 Community Announcements
 4.8K Archive