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.

Find more posts tagged with

Comments

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.