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

Tatyana

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.

MarkSnodgrass

I would suggest you look at these two posts on creating these if you are wanting to display as table cards:

https://dojo.domo.com/discussion/52681/domo-ideas-conference-beast-modes-running-totals#latest

https://dojo.domo.com/discussion/52679/domo-ideas-conference-beast-modes-rolling-averages#latest

If you can display this information as a bar+line graph, I would suggest you use the period over period cards and select last 24 months in your date range filter and group by month and compare to 12 months prior. This would not require any beast mode work and would let you see how a month compared to the same month in the prior year.

Tatyana

Thank you, Mark. I will try rolling summaries. I do need it in the table cards.

GrantSmith

I’d recommend using a date dimension with custom defined offsets. Using the custom date offset dimension you can have the current value and the value from 12 months ago. This will simplify your graphing and correctly calculate the current month to the partial month from the previous period . I’ve done a Previous write up here: https://dojo.domo.com/discussion/53481/a-more-flexible-way-to-do-period-over-period-comparisons#latest

Tatyana

Thank you for reminding about this, Grant! And, if they want to choose 12 months to compare to the previous time period, they could do it by using Date page filter or calendar filter card, right?

GrantSmith

Yeah, it'll automatically filter based on the date or card filters, the period offset won't be able to change but the timeframe they're looking at can. You can always define additional offsets if necessary.