Calculating a final value from two selected values that exist in the same column?

muahmmad_zaidi

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.

GrantSmith

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.

marcel_luthi

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 🤣

GrantSmith

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.

muahmmad_zaidi

Cool! Can I duplicate the columns in Magic ETL?

marcel_luthi

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 🤣