Rank & Window Function
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!
Best Answers
-
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 any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
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!
1
Answers
-
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 any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
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!
1
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 293 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 96 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 705 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive