Skip to content

    • Magic ETL

    Magic ETL

    Rank & Window Function

    Member
    in Magic ETL

    Hello,

    I am trying to use the rank & window function to grab the latest date in a column, but am confused as to how to set up the tile. Currently this is my set up:

    image.png

    I just want to grab the latest date, but it says that the tile/action is not being configured proprely. Any advice?

    Many thanks in advance!

    Best Answers

    • Coach
      Answer ✓

      In the first part of the tile, what did you choose for the "function to apply". I would suggest choosing Rank and then you should be able to use what you have selected for the second half, assuming PaidDate is a date. The next step would be to add filter tile after this and filter to where rank = 1, which would get you the latest paid date.

      Another option would be to use the group by tile instead and choose the aggregation type of Max for your Paiddate field.

      **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.
    • Coach
      Answer ✓

      What about step (1) where you need to define the formula to use? Do you need to grab the Latest Date from the whole dataset or partitioned by something (like the last time for each Id, category, etc). Based on your description you don't need a Rank and Window tile, but a Group By Tile instead, your ETL might end up looking something like:

      image.png

      Where you have a constant that applies to all Rows, and you get the Max ID for all of them. If your Max date needs to be scoped you do so by selecting the grouping columns, and once you have the value you do a Join back to the original table to append the column.

      image.png

      Keep in mind that any Aggregation that happens in ETL will not be responsive to page/view level filters.

      Hope this helps!

    Answers

    • Coach
      Answer ✓

      In the first part of the tile, what did you choose for the "function to apply". I would suggest choosing Rank and then you should be able to use what you have selected for the second half, assuming PaidDate is a date. The next step would be to add filter tile after this and filter to where rank = 1, which would get you the latest paid date.

      Another option would be to use the group by tile instead and choose the aggregation type of Max for your Paiddate field.

      **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.
    • Coach
      Answer ✓

      What about step (1) where you need to define the formula to use? Do you need to grab the Latest Date from the whole dataset or partitioned by something (like the last time for each Id, category, etc). Based on your description you don't need a Rank and Window tile, but a Group By Tile instead, your ETL might end up looking something like:

      image.png

      Where you have a constant that applies to all Rows, and you get the Max ID for all of them. If your Max date needs to be scoped you do so by selecting the grouping columns, and once you have the value you do a Join back to the original table to append the column.

      image.png

      Keep in mind that any Aggregation that happens in ETL will not be responsive to page/view level filters.

      Hope this helps!

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories