MTD Last Year, YTD, YTD Last Year with Rank & Window Tile

verytiredgirl
verytiredgirl Member
in Magic ETL

Say my dataset has these columns: branch_id, gl_id, amount, Month End Date, fiscal_year, Month Number
I'm trying to recreate below SQL Window function with Magic ETL so that for every Month End Date*branch*glid combination has a corresponding MTD Last Year, YTD and YTD Last Year.

SUM(amount) as 'MTD'

SUM(SUM(amount) OVER (PARTITION BY branch_id, gl_id, fiscal_year)) as 'YTD'

Use LAG() to get the MTD & MTD Last Year:

LAG(MTD, 12) OVER (PARTITION BYCTE.branch_id, CTE.gl_account_id ORDER BY CTE.[Month End Date]) AS 'MTD LastYear',
LAG(YTD, 12) OVER (PARTITION BYCTE.branch_id, CTE.gl_account_id ORDER BY CTE.[Month End Date]) AS 'YTD LastYear'

P/S: fiscal year starts from Feb this year - Jan next year. So Feb 2024 - Jan 2025 has fiscal_year = 2025

My YTD in Magic ETL, but I'm not sure if it sum up correctly for January. Say Jan 2025 YTD = sum(MTD) from Feb 2024 up to Jan 2025

image.png

Answers

  • GrantSmith
    GrantSmith Coach

    Since your FY starts in February, you'll need to re-order your month numbers so that Feb = 1 and Jan = 12 (shifting the month numbers down by 1)

    CASE WHEN `Month Number` = 1 THEN 12 ELSE (`Month Number` - 1) END

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • verytiredgirl
    verytiredgirl Member

    @GrantSmith thank you, it worked for the YTD. Also is it possible to use Rank & Window Tile Lag to calculate MTD Last Year & YTD Last Year? Or should I just go with Formula Tile instead?

Categories