For the selected table, I need to sort by top 10 sites based on this calculated field ‘TAs >15’, however the filter doesn’t let me sort by a calculated field, only fields present in the data set. It is currently incorrect as it ranks based on ‘Combined OTR’ hours instead of counts for ‘TAs>15’. I tried going into the dataset on QS and adding this calculated field which works and gives the correct count and produces same result as the calculated field within the analysis. Even though the calculated field now appears in my filter dropdown after being added into the dataset, it still does not let me use this as a condition to filter. Instead it returns an error saying “Please contact the Quicksight team to resolve this issue”. For extra context: this calculated field ‘TAs >15’ uses sumOver with an aggregated operand to sum over week, region, site, employee. Any ideas for how to properly return top 10 sites with the most count? Thanks!
Hello @Kelly_Cooper, welcome to the QuickSight Community!
So in this case, what I would recommend doing is creating a 2nd calculated field with an ifelse statement to determine if your conditional is correct, rather than set it directly in a filter. It would look something like this:
ifelse({TA} > 15, {calculated_field}, NULL)
Then you can add that field into your pivot table instead of the field that is returning your values directly. To ensure this doesn’t add any rows or display null values, you can add a filter for the new calculated field, set it to a custom filter, select “Does not equal” in the dropdown and enter an impossible value in the text box. Then, make sure you have “Exclude nulls” selected in the final dropdown. This should handle the filtering how you are expecting!
I will mark this as a solution, but please let me know if you have any follow-up questions about the implementation process and I can help you sort those out. Thank you!
Hey @DylanM thanks so much for the reply.
The solution you proposed is not giving my desired results. Creating another calculated field to check if the first conditional is correct returns a boolean value instead of keeping a count like the original calculated field does. In the picture, the null sites can be expanded to login detail to show 1 for all 27 TAs in ABE8 for example, which is not really useful as I will need an aggregated view. I would like for the filter to select top 10 site based on the highest counts of ta_count_over_15 or ‘TA>15Hrs’. In current state, I can only return top 10 sites based on Combined Hours not TA count since TA count is a calculated field. The secondary calculated field I created with your recommendation follows the same behavior in that it is not populating in BY for the site filter. Any other recommendations would be really appreciated!
Hello @Kelly_Cooper, okay, I think I understand, we can try something else. Maybe you could consider implementing a denseRank function based on the results of the TA count that you could then use to filter on. If it returned the rank in DESC order for the sites based on the count, then you could add a filter to the table to return the top 10 values by saying less than or equal to 10.
Does that get you closer to your desired solution?
Hello @Kelly_Cooper, did my last reply help you achieve your expected output? If so, please mark it as the solution. Otherwise, let me know what other issues you have encountered trying to implement the denseRank and I can help guide you further. Thank you!
@DylanM Thank you for the reply. Here I have created a calculated field using denseRank and defined as follows.
It works somewhat as expected but doesn’t give me the top 10 results I am looking for. In the bottom left image we see sites with 3 TAs (a few tied like ACY5 and TEB3) are dense ranked as 9. This whole list returns 30+ sites total due to some tied values.
In this excel image, we can see how tied values lead to more than 10 sites appearing. Ideally, Id just like to display to top 10 sites MDW2 ending at SDF8. Changing the filter value for ranks 1-7 will yield those results for this week, however I know next week it could differ depending on how many ties we have. Is there any way around this? Secondly, I’d like to display the results in desc order as shown in the excel screenshot.
Hello @Kelly_Cooper, I think you would want to partition by your Site field as well as Report Week in the denseRank aggregation. That way each site will get a distinct rank that you could then filter by.
Then if you wanted to sort descending, you should be able to do that in the pivot table by clicking the TAs > 15hrs column or rank column and then applying the sort. The option also exists in the field well when you select the 3 dots.
Hello @Kelly_Cooper, did my last response help guide you towards your expected output in your visual? I’ll mark it as the solution for now, but if you have any further questions or are still running into an issue on implementation, please let me know. Thank you!
Thank you for the support on this. Was able to resolve