Hi, I am new to QuickSight. My dataset is as follows and has columns employee id, plan name and current. I want to create a field that shows the maximum (alphabetically last) plan name for the whole dataset where the current field is 1. So in the below example its 2024. I want to then use this field set a default value in a parameter.
| Employee ID | Plan Name | Current |
|---|---|---|
| 1 | 2022V3 | 0 |
| 1 | 2023 | 0 |
| 1 | 2024 | 1 |
| 2 | 2022 | 0 |
| 2 | 2023 | 1 |
| 3 | 2022V1 | 1 |
Any help would be greatly appreciated.
Hello @rslabicki , welcome to the Quicksight community!
I think you should be able to achieve this with denseRank:
Let me know if this helps!
Hi @duncan,
Thank you for your reply, please could you give me the example code based on my field names above?
Hi @gillepa, thanks for this, I’m not sure this gives me what I want though. For every row in my data set I want the field to display 2024 as this is the maximum value with current = 1 across the whole data set. If I create your field it asks me to add Plan Name and Current to the field wells when I visulise the data. When I do this it just shows the maximum for that row of data not the whole data set.
Hello @rslabicki !
Could you try “hiding” those two columns to see if that helps?
Hi @rslabicki, did this answer your question? If so, please help the community out by marking this answer as "Solution