Lag() function for previous value over unpredictable rows counts of each partition
Hi Everyone,
I have a dataset with a monthYear
date field, a Quarter
field with values 1-4 to represent quarters, and finally a quarterly_value
column.
Here's where it gets fun.
Not all months and quarters are present in the dataset.
There will never be a monthYear
with more than one row (i.e. never two rows of DEC 2023).
I use the Rank & Window LAG() function, using offset = 1.
I Order the functions on monthYear
in DESC order
I partition over Quarter
.
My results are that I can get the previous value on the first row in a quarter (ex: JUL 2023), but then it will still show the current Quarter's value for AUG and SEP 2023.
Results —>
How can I, instead, get the lag_results to propagate over all rows of that quarter?
Desired Results —>
Thank you again, DOMO community!
Best Answer
-
My understanding is that you are looking for the final value with in a quarter to be the lagged value shown for each month of the following quarter. Wouldn't that mean the lag_result for 2023-05 and 2023-06 being NULL as well? Here is the solution I came up with:
If I solved your problem, please select "yes" above
0
Answers
-
My understanding is that you are looking for the final value with in a quarter to be the lagged value shown for each month of the following quarter. Wouldn't that mean the lag_result for 2023-05 and 2023-06 being NULL as well? Here is the solution I came up with:
If I solved your problem, please select "yes" above
0 -
I am so thankful for people like @ColemenWilson in this community!
Your solution gets me so close to my end goal, and lets me think through the data in new [for me], enlightened way.
There is one hiccup I am trying to wrap my head around.
The dataset [shown below] has 5 unique quarterly values.
The fifth value is for our current Q2 2024, and cannot be carried over yet (logical, no rows for the future, JUN 2024+).
The lag function should work for 4 of those values.
It works for the first two, skips the third, and works again for the fourth.
Data below to make sense of all of this. Lag skips for Q1 2024 —>
I am going to keep plugging away; I just wanted to thank you @ColemenWilson asap :)0 -
I believe the issue lies in joining on the year.
However, if we do not join on the year, we get duplicate rows.0 -
I added the data for 2024 you provided above and the ETL I shared produces the correct lag_results values without nulls.
If I solved your problem, please select "yes" above
0 -
@ColemenWilson I realize now my blunder in replicating your perfect solution.
Apologies for the extra work there. I made a copy of the ETL from scratch using your solution, and it worked. I went back to the original and realized I had messed up the Rank and Window, by Ordering the function onQuarter
instead ofMonthYear
.
Thank you once again. I see you're in UT, I used to work for BEN Labs up there. Look them up if you ever want a change of scenery from Henry Schein (I looked you up, impressive stuff ;) ).
I wish I could buy you a beer as a thank you!3 -
@emmanuelfabre_1 you're welcome! Next time you are in Utah let me know and I'll take you up on that!
If I solved your problem, please select "yes" above
1
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
- 100 SQL DataFlows
- 621 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 743 Beast Mode
- 58 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
- 124 Manage
- 121 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