I am trying to figure out how to rank carriers based on multiple fields.
My visual is set up as a pivot with carrier as my row, Pick up week as my column and carrier performance *2Carrier OTO (calculated field) as my value.
Is there a way to get bottom 5 carriers based on total loads largest to smallest and then *2Carrier OTO (%) smallest to largest? Also using the most recent week as the column receiving the sort.
I have tried creating a SCAC filter as a top and bottom filter type and *2Carrier OTO and Total Loads as my filter conditions but that does not work for my use case.
Thanks for posting your question and welcome to the QuickSight Community.
If I understand correctly, are you adding *2Carrier OTO and Total Loads as separate filter conditions on the same top and bottom filter?
If so, does adding only one filter condition for *2Carrier OTO to the top and bottom filter and then using the column sort on the most recent week column in the pivot table meet your use case?
Hi @abacon. Thank you for the reply. I decided to go with most delays instead of volume and %.
With that being said, here is the new question I have. I created a top and bottom filter with “total delays”, however since I am looking at 6 weeks worth of data, it is adding all weeks to determine carrier with most delays. Instead I would like for the carrier with most delays to be only for the latest week and still would like to see prior weeks to determine if there was a trend in performance.
Example, here is prior week of carriers with most delays sorted for 1 week (prior week). This is what I would expect to see in my visual.
Here is what I get with the new applied filter. Is there a way to only rank most delays based on the most recent week instead of all?
Hello @caszluis, is this an issue you are still facing in QuickSight? I am wondering how you are currently calculating total delays that it is ignoring the week in which it exists in the pivot table. You could try building a total delays field that is partitioned by weeks, or even build a rank calculation that will rank the delays based on the most recent week that you could then sort by.
I will say, even in the QuickSight documentation, it is recommended to simplify pivot tables as much as possible to achieve the best results. Once you get to a point that you are trying to sort and filter by too many different variables, it may be worth considering splitting it into 2 different visuals. To sort by the most recent week’s delays, I would definitely recommend either a rank aggregation, or create a calculated field that is an ifelse statement, to return only delays for the most recent week. thisWeekDelays = ifelse(truncDate('WK', {date}) = truncDate('WK', now()), {delays}, NULL)
Then running a LAC-W function to get the total for each group based on the current week. distinctCountOver({thisWeekDelays}, [{Carrier}], PRE_AGG)
These would be some options to accomplish what you are looking for. Let me know if you have any further questions.
