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

Tatyana
Tatyana ⚪️
edited October 2021 in Dataflows

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

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷

    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.




    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Thank you, Mark. I will try rolling summaries. I do need it in the table cards.

  • GrantSmith
    GrantSmith Indiana 🥷

    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



    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**
  • 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
    GrantSmith Indiana 🥷

    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.



    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**