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

user095063

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.

MarkSnodgrass

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

 

GrantSmith

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

jaeW_at_Onyx

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