Hi All,
I’m having following fields in my dataset:
createdOn : javascript timestamp at which customer record got created
timestamp : a quicksight calculated field which converts ‘createdOn’ to readable time stamp i.e. dd-mm-yyyy format
MonthAndYear : a quicksight calculated field which extracts month and year from ‘timestamp’ field and gives data in ‘MMM YYYY’ format i.e. JAN 2023, FEB 2023 etc
Now I want to create a quicksight pivot table which creates number of customer records created for each department month by month. I’ve added departmentID to rows and MonthAndYear field to columns , but couldn’t order of columns seems to be alphabetical (i.e. April comes first and then February etc). How can I sort these columns in the actual format (i.e. dec 2022, jan 2023, feb 2023 … etc) ?
Hi @shiva2gandluri
You could create a calculated field that concatenates the year and month as numbers, and sort by that.
For example, a calculated field might look like: parseInt(concat(toString(extract('YYYY', TimeStamp)), ifelse(strlen(toString(extract('MM', TimeStamp))) = 1, concat('0', toString(extract('MM', TimeStamp))), toString(extract('MM', TimeStamp)))))
This gives you a format of 202212, 202301 etc.
You can add this to your pivot table, and then sort by it. Since you don’t want to see it you can also hide it and you will still be able to sort on it.
You need to add it in the right place in the rows section in order for it to work, so you could add it before department to always sort by date first, or after department so you will see department and then sorted by date. If you add it after your MonthAndYear field the sort won’t have any effect. Here I am grouping by department and sorting by date
