To sort the values appearing on a date dropdown in descending order

Question & Answer
, , , , ,
1

I have created a date dropdown control, based on the requirement
image

Since we don’t have any option to display this in descending order, I need to attach some prefix to display the latest date first. I am attaching my Report month calculation here, How can I add a prefix to this report month so that the dates will appear in descending order using quick sight calculation?

These are the calculations I am using to calculate report month (string datatype).

Year =toString(extract(‘YYYY’,{report_date}))
Month= extract(‘MM’,{report_date})
MM=ifelse(Month=1,‘01’,
Month=2,‘02’,
Month=3,‘03’,
Month=4,‘04’,
Month=5,‘05’,
Month=6,‘06’,
Month=7,‘07’,
Month=8,‘08’,
Month=9,‘09’,
Month=10,‘10’,
Month=11,‘11’,‘12’)

Report Month= concat(Year,‘-’,MM)

Please help to attach a prefix to this from quicksight side, so that I can display the Report months in descending order

1 Like
2

Hello @Ganga, I was able to write out some calculated fields to make this work! We can use a denseRank calculation to rank the dates by month descending. That will be how we control the value added in front. To build that, I also created a calculated field to convert the date value to a month.

Date Month = truncDate('MM', {report_date})

Rank Dates =

denseRank
(
  [{Date Month} DESC], 
  [],
  PRE_AGG
)

Report Month =

concat(
ifelse({Rank Dates} <= 9, 
concat('0', toString({Rank Dates})),
toString({Rank Dates})),
': ',
toString(extract('YYYY',{report_date})),
'-', 
ifelse(strlen(toString(extract('MM',{report_date}))) = 1, 
concat('0', toString(extract('MM',{report_date}))), 
toString(extract('MM', {report_date}))))

These 3 calculations should provide the result you are looking for! I will mark my response as the solution, but let me know if you have any questions. Thank you!

3

HI @DylanM ,

Thankyou So much for the response. I tried with this calculations, and I was able to achieve this as a field. But while trying to pull this to controls I am getting error msg showing that to make as a custom filter and if we make that we have to explicitly specify the values. Is there any way to resolve this, so that we can directly use that Report Month field as normal control?

image
image607×670 42.7 KB

1 Like
4

Hello @Ganga, I wonder if it would bypass this issue if you built the calculated field on the dataset, then used a parameter based control to populate the list from the dataset and liked it to a filter that way.

5

Hi @DylanM ,

Thanks For your suggestion, I have created the same calculations at dataset level and tried the same, and in report created a parameter to link this . But still the same error i am getting. If i display this in visuals, its populating, but controls is still showing this error

image
image1381×679 80.8 KB

6

Hello @Ganga, I am wondering if it is because we are using the denseRank calculation and it is overcomplicating the field. That is the only thing I can think of that might be causing you to run into this limitation.

Are you utilizing any custom SQL, whether in QuickSight or in your database, where you could set the rank values ahead of time? Maybe if we can reduce the number of calculations leading into the final field, we can get it to run correctly.

7

I have solved it by adding the prefix from database side. Thanks!

1 Like
8

Hello @Ganga, thank you for letting me know! I wish I fully understood why the original function didn’t work, but it just seems like we hit a limitation. Updating the query is always an option to manage some of these custom field updates.