Can I dynamically group data to two periods (Current 12 month and Prior 12 months) ?

Tatyana
Tatyana Member
edited October 2021 in Magic ETL

Can I dynamically group data to two periods (Current 12 month and Prior 12 months) using window function so I can select time periods to compare. Client wants to be able to select months in the past and compare rolling 12 month as of the last date select date.

I tried this formula for Current period, but it doesn't aggregate all the way.

case when dense_rank() over (order by `Posting Period` desc) <=12 then sum(`Payment Amount`*-1) end

When I remove Posting Period column from the table, the Current Period Payments column doesn't aggregate and remains the same. i want to be able to add Prior 12 months data Next to Current.


Answers