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

Mickey
Mickey Member
edited March 2023 in Scheduled Reports

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:

Date From | Date To | Units Sold | Retail | Expenses | ...

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:

Date From | Date To | Units Sold | Retail | Last Period Units Sold| Last Period Retail |

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.

Tagged:

Comments

  • Mickey
    Mickey Member

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

  • James_Clark
    James_Clark Member
    edited March 2023
    example.PNG

    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.

Categories