Error whilst sorting a pivot table with a lac function

Hi,

I’m currently getting an error whilst trying to sort a pivot table visual.

I have a lac-a calculation which gives the correct numbers, it only errors when I try to sort by one of the other value columns in the pivot. Without sorting the pivot works as intended, I am able to collapse and expand with no problems.

Removing the lac-a calculation stops the error but I would like to keep this calculated field in the pivot table.

Any alternatives ways of doing this?

Hello @jonnyp, pivot tables can be a bit difficult to format in a way that will display properly and allow for some of these dynamic sorting options. In order to try and figure out a fix for this issue, I will need some more information. Are you able to send an anonymized view of the visual and the calculated field you are using within it? It may also be helpful if you are able to put it in a QuickSight Arena environment and post the link. Either should help provide me with some more information, so whatever works best for you. Thank you!

Hi Dylan,

I’ve created an example dashboard and dataset and published to the arena. Here is the link:
https://community.amazonquicksight.com#Arena-dashboardId=73707aff-a42d-4564-8eef-e158ff6262eb&authorId=CU-10629

The table shows the underlying data and the pivot table shows the error if you try sorting by the 2nd order id column (the count distinct one).

Thanks!

Hello @jonnyp, I think I figured it out! You can see it in the visual that I duplicated in arena. Basically, instead of partitioning the min function in your calculated field and then calculating the average, I set the field to just min({Order amount}). Then, click the 3 dots on the field in the field well and set the Totals aggregation to average. Now you are able to sort without any errors.

Thank you for taking the time to add this into Arena. It makes it a lot easier to assist you when I am able to test my ideas on the visuals. I’ll mark this as the solution, but let me know if you have anymore questions!

1 Like

Thanks Dylan, this works for this use case!

Could this be a potential bug though that the calculation works and is visible in the pivot table but only throws this error when sorting?

1 Like

Hello @jonnyp, I am glad that resolved it! That is a good question though. Honestly, I don’t know if I view it as a bug but rather that the aggregations are confusing and have certain limitations. I can tag it as a feature request though! The QuickSight calculated field aggregations can often get fairly complicated when trying to utilize them in a pivot table. That is why I try to manage as much as possible within SQL to simplify the aggregations.

1 Like