lag function

Jones01 · 2022-10-19T12:40:10+00:00

There was an error rendering this rich post.

Jones01

Hi everyone,

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.

i.e.

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.

Thanks

Find more posts tagged with

Accepted answers

MarkSnodgrass

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.

All comments

MarkSnodgrass

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.

Jones01

@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.

from Oracle.

lag(value, date-add_months(date, -12) over (partition by Id order by date)