Comparing one month's EOM data to previous month EOM data in same row

TMonty0319 · 2023-10-12T19:14:00+00:00

There was an error rendering this rich post.

Hi,

I need to do a calculation so I can subtract previous months hours (4/31/2023) data from this month's hours (5/31/2023) data. This is how data is organized. This just a small example of how it is. Without changing the structure of dataset how can i get all the 4/31 data on the same row as the 5/31 data? I have been trying to use rank and window but haven't had any luck. Also tried some formulas to create a previous month column but still stuck on this.

Facility	Date	Engine	Hours
Facility 1	4/31/2023	Engine A	34564
Facility 1	4/31/2023	Engine B	76574
Facility 1	4/31/2023	Engine C	4567
Facility 2	4/31/2023	Engine A	34566
Facility 2	4/31/2023	Engine B	45667
Facility 2	4/31/2023	Engine C	23455
Facility 1	5/31/2023	Engine A	54652
Facility 1	5/31/2023	Engine B	65433
Facility 1	5/31/2023	Engine C	22344
Facility 2	5/31/2023	Engine A	12334
Facility 2	5/31/2023	Engine B	455654
Facility 2	5/31/2023	Engine C	34455

Find more posts tagged with

Dataset

Magic ETL

Formulas

Accepted answers

ColemenWilson

You'll want to filter out rows of data that are not the "last day" of data. You can then use the LAST_DAY() function on the date to get the last day of the month (not necessary, but optional). Then you just need to do a lag offset of 1 because your data would look like this:

Screen Shot 2023-10-25 at 9.00.07 AM.png

So if you partition by Facility and Engine and lag 1 row you'd get the value for the last day of the month for the previous period.

All comments

ColemenWilson

I would use a lag function in an ETL to get the previous value. You'll want to use the rank and window tile, sort on your date field, use an offset of 1 and partition by facility and engine I would assume.

TMonty0319

Thats what have been trying but can't seem to get data to carry over correctly. Ill keep trying. Any other options?

marcel_luthi

How have you configured your Rank and Window tile? You should be able to do:

Function of LAG 1 on the Hours
Order by Date ASC
Partition by Engine and Facility

As @ColemenWilson suggested, unless there are other columns in your structure that could cause multiple entries for each month.

TMonty0319

Yeah im trying that but for some reason the values I get in this new rank and window column are the values in the same month one record above. So for some reason its not grabbing previous month and just previous record of same month

Facility 1	5/31/2023	Engine A	54652	New Column
Facility 1	5/31/2023	Engine B	65433	54652
Facility 1	5/31/2023	Engine C	22344	65433
Facility 2	5/31/2023	Engine A	12334	22344
Facility 2	5/31/2023	Engine B	455654	12334
Facility 2	5/31/2023	Engine C	34455	455654

ColemenWilson

You need to partition on both facility and engine. This is an optional step that is needed in your use case. You'll see in the image below that you can partition on multiple columns:

Screen Shot 2023-10-12 at 6.58.56 PM.png

TMonty0319

When I used on offset of 30 I started getting some results. I need to go back 30 days to get end of last month data. Problem is some locations might report on 5/28 and some might be 5/29 and then next month might be 6/29 or 6/30. I cant just set it as 30 or wont capture all the data. Any suggestions on how to maybe have a range or some way to match up max values if arent exactly 30 days apart. Trying to think about how can use a Max date

ColemenWilson

So if you partition by Facility and Engine and lag 1 row you'd get the value for the last day of the month for the previous period.