Best Of

Regrettably, window functions aren't supported in Magic ETL formulas, and as you've already determined it's not possible to express that window frame using the Rank & Window tile. Including the frame boundaries in the original data, using Beast Mode, or using the Python Script tile are all options.

If the whole input dataset is reasonably small (say, less than a million rows) you could probably do it with a cross product join of the table against itself:

EDIT: There's a better way to do this than I had originally described. Rather than a full cross product, cross products within each Company result in less intermediate rows and obviate the need for the company equality check later.

1. Make sure you have a row key of some kind; that is, one or more columns that can together uniquely identify a row.

2. Join the data to itself using Company as the join column. Since you can't connect one tile to another "doubly", send one connection to a Select tile first and select just the Score, Company, and Date columns. Give them new names as well so they don't collide; prefix all of them with Other_

4. Add Formula next, with a column called in_frame using this expression: CASE WHEN DATEDIFF(Date, Other_Date) < 91 THEN 1 ELSE 0 END

5. Now a Group By, using the "row key" column(s) that we mentioned we'd need in step 1 as the grouping key. You'll need to add an aggregate for every non-grouping column that you want to preserve in the output as well; this can just be "First value". For the window calculation, add the aggregate expression: SUM(CASE WHEN in_frame THEN Score ELSE 0 END) / SUM(in_frame)

How to alter a column that is in text and cast data type as integer or floating decimal?

I get this error when using the Alter Columns tile: 'Failed to parse text ' ' from column 'Column A' as type 'Integer'

How can I alter the columns data type in the magic ETL successfully?