How to pull through top ranking fields on individual basis

Hi All,

Looking to produce a table in Quicksights which shows the top 10 workers who have worked in the past month based on their length of service, this table needs to show the most commonly occurring field in each of the attached column headers.

When trying to filter for the top ranking field for columns 3/4/5/6/11, this is only pulling through the top ranking field for everyone as a whole and not splitting by individuals.

Is there a way that we can get the top ranking field for each individual for these columns?

Thanks
Jake

So you would first add a top and bottom rank filter to the table.

In the top rank you would filter to the worker and then say top, show top 10, by length of service. You’ll need a field for length of service.

Once you have that can you explain further what you want to display with these columns besides just their values?

Why can you just use their Job role? Does this change? If so can you use lastValue by a date or something?

1 Like

Hi Max,

Thanks for your response - I’ve been trying to sort by the length of service but as this is a calculated field I’ve had to produce, the filters are only allowing me to select ‘custom’ as a filter option so I’ve not been able to calculate the top 10 off the back of this.

image

In terms of the columns, this is to produce an export for feeding into a report. The data for columns 3/4/5/6/11 can only accept a single value. The issue I’m having is that if, for example on the Departments section, the worker had worked 4 shifts in department 1 in the period and 2 shifts in department 2 then we would only want to pull through Department 1 for this column.

When I’ve tried to do a top ranking filter on department, I can’t pull it through on an individual basis (in other words for each of the top 10 longest serving workers) and it instead only pulls through the most common department for all workers combined whereas the data needs to be at an individual level, potentially against the Agency Worker ID?

This needs to be the case for the data in each of the columns listed above where some are currency based figures and some are text based such as department or job roles worked.

Hope this explains things a bit better than previously but equally, happy to provide anything else which might help to clear this up.

Jake

For the top ten make a filter on the worker and do a top 10 of the worker based on the term length.

In regards to the per individual you should partition by the individual.

lastValue
(
{column_3},
[ {time_field} ASC_or_DESC, ],
[ {worker_field}]
)

Hi Max,

Thanks for your response on this, I’ve tried to then add these calculated fields into my table but I can see that I’m getting the error message: ‘Custom aggregation field is not allowed as a dimension’?

You need to put it as a value.