50 day rolling average in ETL? Is it possible.

KenTravels
KenTravels
in Magic ETL

I have seen solution to a 5 day rolling average with 5 lines of code to build to 5 days, gets big with a 50 day average

 

Do not want create 50 extra columns to build the rolling average.  

"it is not the strongest or the largest that survives but the ones most adaptable to change"

Comments

  • RACERX
    RACERX

    Not sure of specifics, but you could use a sub-query (MySQL/Redshift) or fork your MagicETL to filter for (roughly) `Your Data Date` <= CURRENT_DATE - '50 Data Days' then take an AVG of whatever the quantity column is. Once you have that, join it back to the main query or branch.  Assuming your data updates daily/regularly, then this average should stay current. 

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    do you HAVE to have it in ETL?  b/c you could shape your data by creating a Date Dimension

    with two columns Report Date and Activity Date, such that the report date has A Day, and for each day in the Report Date you trail 50 days.

     

    So if my Date table had 5 consecutive ReportDates, it'd have 5 * 50 (trailing) Activity dates.  for a total of 250 rows.

     

    Then JOIN that to your fact Table on the Activity Date = Posting Date using a Fusion.  Then you can put Report Date on the Analyzer axis, and Avg (amount) or whatever.

     

    You can do the same in ETL... but don't... writing a table exploded 50x is foolish.  also if you hardcode the rolling average, then it won't respond to filters. 

    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"

Categories