We have a dataset containing two columns Date, Id and Value.
We are using the lag function to get the value from the previous financial year.
lag(Date, 364) over (partition by Id order by Date)
Wondering how we could use this to get the previous date the year before but matching on the date.
So 1st Feb 2018 compares to 1st Feb 2017. We can't just do 365 as the offset as that would be wrong for some years.
We have used the date dimension stuff to stack the data but for this use case we need the values on the same row.
I would suggest doing this in Magic ETL. You can do this with just a few tiles, the formula tile and the join tile.
In the formula tile, create a field called LastYearDate and use the date_sub function to subtract one year from the date field in your datasest.
You can then use the join tile to join the data back to itself by joining on the last year date field you just created and the date field from the original dataset.
You may need to add additional columns to your join criteria depending on your data.
On the right table, you would rename your metric column to Last Year Value (or whatever makes sense to you)
This will put last year's data next to the current year data as an additional column.
Hope this helps.**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1
@MarkSnodgrass thank you very much, that is great.
We have been "stacking" the data to get prior year comparisons so far which means doing a case when period type = 'This year' then value.... in the beast modes (can mean lots of them and not very user friendly for our users)
I'm wondering if I should just join it up onto one row like you have shown. The number of columns in my dataset would explode though but the row count would remain the same unlike the stacking method.
Have you a preference for doing prior year comparisons?
As a side note
One thing to add to the ideas is probably being able to add a formula to the offset in the lag function so you could do this.
lag(value, date-add_months(date, -12) over (partition by Id order by date)0
- 7.7K All Categories
- 918 Connectors
- 242 Workbench
- 476 Transform
- 1.8K Magic ETL
- 60 SQL DataFlows
- 446 Datasets
- 35 Visualize
- 198 Beast Mode
- 2K Charting
- 8 Variables
- 16 Cards, Dashboards, Stories
- 1 Automate
- 348 APIs & Domo Developer
- 82 Apps
- 14 Predict
- 3 Jupyter Workspaces
- 11 R & Python Tiles
- 241 Distribute
- 59 Domo Everywhere
- 241 Scheduled Reports
- 15 Manage
- 36 Governance & Security
- 23 Product Ideas
- 1.1K Ideas Exchange
- Community Forums
- 15 Getting Started
- 1 Community Member Introductions
- 49 Community News
- 18 Event Recordings
- 579 日本支部