lag function

Jones01
Jones01
in Magic ETL

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

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    I would suggest doing this in Magic ETL. You can do this with just a few tiles, the formula tile and the join tile.

    image.png

    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.

    image.png

    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.

    image.png

    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 <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • Jones01
    Jones01
    edited October 2022

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

Categories