quarterly difference in sum(Revenue) saved in a new column SQL Magic Transform

I need to add a new column to my table that will have the difference between sum of revenue of a new quarter over the last one. I am using SQL Magic Transformation.
My data looks like this:
```
Website                  Year    Quarter    Revenue
cosmo.com            2019         4              10
cosmo.com            2020         1              15
cosmo.com            2020         2               5
fashion.com           2019         4               10
fashion.com           2020         1                5
fashion.com           2020         2               20
```
The desired output is:
```
Website                 Year      Quarter      Revenue       Difference
cosmo.com         2019       4                 10                    +5
cosmo.com         2020       1                 15                    +5
cosmo.com         2020       2                  5                     -10
fashion.com         2019      4                 10                   +10
fashion.com         2020      1                 5                       -5
fashion.com         2020       2               20                     +15
```
I have tried this:

select t.*,
(t.Revenue - lag(t.Revenue) over (partition by website order by Year, quarter)) as difference
from table t;

This should have worked but I received: "Whoops! Something went wrong." Without the error explanation.

Comments

  • Domo's version of MySQL doesn't allow for the LAG function. However, you can use Magic ETL and use the Rank & Window tile to accomplish this. It will also likely run much, much faster if you have Magic ETL 2.0 in your instance. Here's a link to the KB article: https://domohelp.domo.com/hc/en-us/articles/360042922814-Magic-ETL-Tiles-Rank-and-Window

     

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Hi @user095063 

    You could write this as a beast mode on your card as it can support window functions (assuming you have the feature enabled - if not talk with your CSM).

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • @MarkSnodgrass is correct.  if you check version  you're on MySQL 5.6  doesn't support window functions.

    Magic 2.0 is amazing, get it enabled (talk to your CSM) if you don't already.  It will be more performant.

     

    That said, If it were me,

    1) I'd convert Year and quarter into a date type column (probably the last day of the quarter) this will give you more flexibility in Analyzer b/c you can use a broader range of cards and take advantage of the date based filters and period over period comparisons.

     

    2) i would not calculate variance in ETL b/c then you'll have difficulty making your card respond to filters (b/c previous period aggregations are baked into the dataset).  Listen to @GrantSmith  and implement the window functions in Analyzer (you cna use Lead and Lag once it's enabled.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
This discussion has been closed.