Sorting of a column in Pivot table is not preserved after user selected date gets changed

[Amit_Raj]

Hi friends,
I have a pivot table, that changes based on the date parameter user selection.
The sorting(highest to lowest) of a column is not getting preserved when the user selects a different date.
the sorting is preserved only for the dates that were used in Analyse mode.
I am trying to sort it by cost(last but one column),

sorting done in analyse view

sorting not working after user selects a different date- I expect the highest value $4864.41 to appear at the top even for the week 2024-06-19- 2024-06-25

Please help.

Thank you

[prantika_sinha]

Hi @Amit_Raj ,

Can you try sorting the Group Field based on Cost field? That should enable sorting even when dates are changed. Since you are sorting by the specific date field, the sorting order is not automatically carried forward.

Thanks,
Prantika

[Amit_Raj]

Hi @prantika_sinha
Thank you for your reply.
unfortunately the cost field line_item_unblended_cost does not have option for sorting.

any other workaround pls?

Thank you

[prantika_sinha]

For the pivot table, you will need to sort by the row field. However since you have column field as well, it will be wise to use a sortkey to sort the rows.

Step 1: Create a calculated field sortkey based on ranking of current week data.
Step 2: from the rows field well, sort using a off visual field.

[Xclipse]

Hi @Amit_Raj, did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!

[Amit_Raj]

Hi @Xclipse
Thank you for following up.
I attempted to create rank as below, but It results in no data.

rank
(
  [max({line_item_unblended_cost}) DESC], 
  [{cal_current_week}]
)

I would like to add here that I create the custom date range from the line_item_usage_date column using below custom field.

cal_current_week-

concat(
      formatDate(truncDate('DD', {cal_current_week_start}), 'yyyy-MM-dd'), 
      ' - ',
      formatDate(truncDate('DD', ${SelectEndDate}), 'yyyy-MM-dd')
  )

where cal_current_week_start is

addDateTime(-6, 'DD', ${SelectEndDate})

and SelectEndDate is the user input from a parameter.
The dates in the column line_item_usage_date are in below format-
2024-07-06T00:00:00.000Z
2024-07-07T00:00:00.000Z
2024-07-08T00:00:00.000Z

Please help.

Thank you

[Xclipse]

Hi @Amit_Raj, please see if the following tips help:

Unable to format dates

Hi, I am using Athena to feed data to Quicksight. In my Athena query, I’m casting an ISO string to a Timestamp like this. cast(from_iso8601_timestamp(dct.start) as timestamp) startdate2 This appears to ‘work’ in that, in the QuickSight DataSet, it shows up as a date. When I display it in a grid though in QuickSight, it only shows the date. This is despite me setting the format to include the time. [image] Can someone please tell me what I’m doing wrong? Thanks Damien PS - I had more …

Supported date formats - Amazon QuickSight

[Amit_Raj]

Thank you everyone for helping.