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.7K Product Ideas
 1.7K Ideas Exchange
 1.5K Connect
 1.2K Connectors
 292 Workbench
 4 Cloud Amplifier
 8 Federated
 2.8K Transform
 95 SQL DataFlows
 600 Datasets
 2.1K Magic ETL
 3.7K Visualize
 2.4K Charting
 685 Beast Mode
 43 App Studio
 38 Variables
 655 Automate
 170 Apps
 438 APIs & Domo Developer
 42 Workflows
 5 DomoAI
 32 Predict
 12 Jupyter Workspaces
 20 R & Python Tiles
 383 Distribute
 110 Domo Everywhere
 267 Scheduled Reports
 6 Software Integrations
 111 Manage
 108 Governance & Security
 8 Domo University
 25 Product Releases
 Community Forums
 39 Getting Started
 29 Community Member Introductions
 98 Community Announcements
 Domo Community Gallery
 4.8K Archive