How to get Last Week/Last Year/etc through dataflows

Mickey · 2023-03-01T22:44:14+00:00

There was an error rendering this rich post.

A lot of premise here so bear with me.

I have a dataset containing financial data for a 14 day period (Sold Units, Retail, Expenses, etc). Every 14 days I upload the latest data from the raw source online. I load that data into Domo workbench and have it append the new data so that everything is in the same place. So each row added is two weeks of data, then there is a Date From column and a Date To column, signifying the period start-end for that financial period.

A quick example:

01/31/23 | 02/14/23 | 7747 | 300,000 | -50,000 | ...

01/17/23 | 01/31/23 | 6543 | 200,000 | -20,000 | ...

01/03/23 | 01/17/23 | 5555 | 150,000 | -15,000 | ...

and so on down the list...

The Date From and Date To columns are the only dimensions in this dataset. Everything else is a value.

I need to get Last Year/Period/Month/etc columns for most of the columns, but I can't figure out how to grab that information. How do I add/create another column, using Beast Modes or SQL or anything, for Last Period Retail, Last Period Expenses, and so on? I have been trying to figure it out on my own for weeks now. I want to take the columns above, and effectively add this:

01/31/23 | 02/14/23 | 7747 | 300,000 | 6543 | 200,000 |

01/17/23 | 01/31/23 | 6543 | 200,000 | 5555 | 150,000 |

01/03/23 | 01/17/23 | 5555 | 150,000 | xxxx | xxx,xxx |

I need these columns to be able to create %change and %ofWhole fields. It's the number one thing we need.

Find more posts tagged with

Dataset

Beast Mode

Comments

Mickey

Apologies if the bottom "chart" is difficult to read. It was all spaced out but changed when I posted the question

James_Clark

Lots of ways to do this, but basically, you want to join the data from the Date To column to the Date From column. Then you need to rename the Units Sold to Last Period Units Sold, and the Retail to the Last Period Retail. This will add the extra column you are wanting. Not sure what package or permissions you have in your DOMO instance, so would not want to suggest how you go about it. Using SQL and ETLs are my preferred option, but you may have another that will work better for you.