How to get Last Week/Last Year/etc through dataflows
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.
Comments
-
Apologies if the bottom "chart" is difficult to read. It was all spaced out but changed when I posted the question
0 -
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.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 747 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive