This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Hello - I'm very new to this tool. Appreciate all the assistance.
I have a data set with a list of clients, engagements, type of work and revenue by year (5+ years).
I need to get a list of clients for each work type based on max revenue. Type X to be filtered for only 2017-2019 years for max revenue. Type Y the same and Type Z work type will be open to all years. And in that order Type X >> Type Y >> Type Z.
The Key is to not have a client considered again while filtering for max rev if it's already captured before. For instance: if client 123456 was captured under Type X, it should not be in the data set that will be filtered down to capture Type Y clients and not for Type Z clients if they were captured under Type Y.
I've assigned an excel version of the issue. Hope that explains.
There is a tool that is under utilized that is perfect for this use case. The Tile tool allows you to group by fields and then adds flags to each record based on their order in the data. If you do some pre-sorting, this will allow you to filter the way you want. See the attached workflow to further illustrate the point.
another approach is to take a Filter/Join method. You can set up your first filter to look for just Type X, in the year range. The T output are the records you want for the Type X summary. Join that back to the "F", on the Client ID field. Whatever doesn't match from the F side will be what you want to check for your next filter.
Thank you both!! This was great. Tile is a great tool to know as well. I did go with the Filter and Join approach since I think that fit best with what I want to develop further off of this. Thank you again!! Much appreciated.