How to get Prior Year Values on the same table row

LucianLeveles

I’m trying to create a row which shows the “Prior Year” value against a field for comparison, simply a 12 month offset of the data as shown below with the "Value" field. This would later enable me to create YoY differences.

I will also note that I'm looking for a solution that should be able to transcend filters i.e. you see in the table above I’ve filtered so the Payment Month doesn’t go before October 2023, but that the Prior Yr value is still picking up the results from before then for comparison (i.e. 44,256 value is actually from Oct 2022)

I’ve tried looking at various online resources and materials from Domo, but haven't yet found a reference to how this can be done. Some other questions and solutions always refer to a CURRENT_DATE() in the formula, but as you can see above this table is entirely agnostic of a date relative to now. There’s also the “period over period” charts, but none of those help me achieve it for a table as above.

The values in this table are aggregated up to months from granular data.

Note: The above screenshot shows this working in the ThoughtSpot environment, and I'm trying to achieve the same thing in Domo. For reference, here’s the TS formula that achieves this:

group_aggregate (moving_sum(Value ,12,-12, Monthly Payment Date) , query_groups ( ) , {} ) 

To explain it: it’s a window function moving sum. The 12 and -12 refer to “how far backwards” and “how far forwards” do we want to sum respectively. By setting it to 12 backwards and -12 forwards, it actually sets the range to a single month one year ago. It’s a bit of a workaround, but has worked there very well. I haven't seen such moving window functions for Domo beast mode.

ColemenWilson

You can use a beastmode:

LAG(SUM(Value),12) OVER(ORDER BY Monthly Payment Date)

GrantSmith

Alternatively you can restructure your data so that for each date you have the values for the current date and last year to allow for easier filtering. I've done a write up on how to do this here:

https://community-forums.domo.com/main/discussion/53481/a-more-flexible-way-to-do-period-over-period-comparisons#latest

ColemenWilson

You can use a beastmode:

LAG(SUM(Value),12) OVER(ORDER BY Monthly Payment Date)

GrantSmith

Alternatively you can restructure your data so that for each date you have the values for the current date and last year to allow for easier filtering. I've done a write up on how to do this here:

https://community-forums.domo.com/main/discussion/53481/a-more-flexible-way-to-do-period-over-period-comparisons#latest