Rank & Window Function

Victoria_G1

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:

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!

MarkSnodgrass

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.

marcel_luthi

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:

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.

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

Hope this helps!

MarkSnodgrass

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.

marcel_luthi

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:

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.

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

Hope this helps!