Custom sorting on basis of date field within quicksight for chart is not working

Question & Answer
, , , , ,
1

Hi All,
I have parameter with weekly, monthly and yearly as string value within it as shown in attached screenshot.

I am plotting monthly data within graph as shown in below :

image
image1042×151 5.32 KB

However, Not able to sort on basis of date field which is not available within my field shelf. as my calculated dynamic date field will be in string having month name-year for monthly, actual date for weekly and year number format on basis of parameter value selection.

Below is calculated field with expected date labels on x-axis.

ifelse(${SelectPeriod} = “Yearly”,toString(extract(‘YYYY’,truncDate(“YYYY”,{user_onboard_complete_date}))),
${SelectPeriod} = “Monthly”,concat(left(formatDate(truncDate(“MM”,{user_onboard_complete_date}),‘MMM-dd-yyyy’),3),‘-’,right({Year_number},2)),
${SelectPeriod} = “Weekly”,toString(formatDate(truncDate(“WK”,{user_onboard_complete_date}),‘dd-MMM-yyyy’)),
NULL)

Can someone please assist on how we can add custom sorting on basis of actual date field (date as data type) which is not available in visual?

As after applying sorting on above created custom calculation, Its not sorting my X-axis correctly as those date labels are in string.

Let me know in case you required more details please.

2

Hi @vaibhavt
to make sure. You have a date field within your dataset?
Did you try to do custom sort for the x axis based on the date field?
BR

1 Like
3

@ErikG Thanks Erik for your quick response on this.
We have date field within dataset but not using within visual. Tried applying custom sort from field shelf, Its not working for me.
Below are steps that we followed.

My X-Axis field have date format which is in string. Requirement is to sort X-axis on basis of date and not on basis of string formatted date labels which we are using within X-Axis field wells.

image
image1086×327 39.7 KB

4

What do you mean by “isn’t working for me”? Doesn’t it sort at all or not the expected order?

5

@ErikG Custom Sorting is not working as expected. It should consider date and on basis of that custom sorting date field, X-Axis should get sorted. Its not working.

image
image1278×299 29.7 KB

I believe, Its considering aggregation on date field for custom sorting and not able to sort directly from off visual date field.

6

Hi @vaibhavt
you could try to create a numeric field e.g. 202312, 202402 and use it as sort order (with aggregation avg).
BR

7

Hi @vaibhavt
could you solve your issue?
BR