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

muahmmad_zaidi
muahmmad_zaidi Member
edited June 2023 in Cards, Dashboards, Stories

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.

Tagged:

Best Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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!**
  • marcel_luthi
    marcel_luthi Coach
    Answer ✓

    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:

    image.png

    Just for comparison we can get the total revenue per quarter

    image.png

    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)

    image.png

    So your card will look something like:

    image.png

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

    image.png

    Tada!!! you have a way to do what you wanted without additional ETLs or datasets, just in a very weird and odd way 🤣

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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!**
  • muahmmad_zaidi
    muahmmad_zaidi Member

    Cool! Can I duplicate the columns in Magic ETL?

  • marcel_luthi
    marcel_luthi Coach
    Answer ✓

    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:

    image.png

    Just for comparison we can get the total revenue per quarter

    image.png

    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)

    image.png

    So your card will look something like:

    image.png

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

    image.png

    Tada!!! you have a way to do what you wanted without additional ETLs or datasets, just in a very weird and odd way 🤣

Categories