Can I dynamically group data to two periods (Current 12 month and Prior 12 months) ?
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.