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 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.
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.
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’?