Sorting order for Calculated fields (Quarter/Month/Year)

Question & Answer
, ,
1

Hi Team, we are facing the following challenge →
We have a date field and sales corresponding to that in the backend dataset. We are trying to create a pivot table that shows sales by Month/Quarter/Year. The time period is chosen by the user through a parameter and that is used in the calculated field to create the aggregation.
We are using the following calculation -
ifelse(${Trend} = ‘Quarter’, CONCAT(‘Q’, toString(EXTRACT(‘Q’, {sf_dt})),‘-’,toString(EXTRACT(‘YYYY’, {sf_dt}))),
ifelse(${Trend} = ‘Month’, CONCAT(substring(formatDate({sf_dt}, ‘MMM-dd-yyyy’),0,4),‘-’,toString(EXTRACT(‘YYYY’, {sf_dt}))),
ifelse(${Trend} = ‘Year’, CONCAT(‘FY-’,toString(EXTRACT(‘YYYY’,{sf_dt}))),null)))

Now the problem is whenever we are selecting ‘Quarter’ as parameter value, the column names aren’t sorted properly. We need to display the columns like → Q3-22, Q4-22,Q1-23,Q2-23 etc. The sorting is fine for a year but when more than one year is selected, the issue comes up.

I have also tried to order it by an off visual field and choosing the parent date field. However that is asking me to choose the aggregation as count/ count distinct for ordering which doesn’t solve the purpose.

Thanks,
Atreya

1 Like
2

Hello @atreya, welcome to the QuickSight community! When you say it isn’t filtering properly, how are the values returning for Quarter? It likely has something to do with the fact that you are switching the date to a string field so it is no longer to sort by date but rather is sorts by alphabetical order, so I can see that causing an issue. It may look a little strange, but does it sort better if you put the year value before the Quarter? If you format it like this 22-Q3, 22-Q4, 23-Q1, I think it might work better.

3

Hi Dylan, the values are currently like Q1-2023, Q2-2024. I agree with your approach of using 22-Q3, 22-Q4, 23-Q1 but our client wants to display it like Q1-2023, Q2-2024 if possible.

4

Hello @atreya,

This can be achieved using off-visual field sorting, see Sorting visual data in Amazon QuickSight - Amazon QuickSight

I added a calculated field to my dataset using the following calculation and called it ForOrdering:
parseInt(formatDate({Order Date},'yyyyMMdd'))

Then, in my analysis I can use that field to sort the pivot table using the calculated field you defined (called ConcatDate in my example below). Clicking the ellipsis next to the ConcatDate field in the field well, then “Sort by:” → “Off-visual field…”

image
image792×406 50.7 KB

Then select the ForOrdering field, set aggregation to “Min” and sort order to ascending, then click apply, should result in a pivot table like this:

image
image728×564 55.9 KB

Let me know if this provides a solution and, if so, please mark my post as Solution to help the rest of the community.

Many Thanks,
Andrew

1 Like
5

Thanks a lot for your detailed post @abacon. Through this solution, I was able to order it correctly for quarter but it is causing issues with the month level ordering. We have a toggle field called Frequency which stores values → Month/Quarter/Year.

Monthly -

image
image2680×126 10.3 KB

Ideally when the user selects Quarter, the date based sales should show up as Q123, Q223, Q323 etc. and on choosing month, it should get displayed as Jan-23, Feb-23, Mar-23.

Thanks,
Atreya

6

Hi @atreya,

If I understand correctly you would like to display the months as “MMM-yy”. To achieve this I modified your calculated field as follows:

ifelse(${Trend} = 'Quarter', CONCAT('Q', toString(EXTRACT('Q', {Order Date})),'-',toString(EXTRACT('YYYY', {Order Date}))),
ifelse(${Trend} = 'Month', CONCAT(substring(formatDate({Order Date}, 'MMM-dd-yyyy'),0,4),'-',substring(toString(EXTRACT('YYYY',{Order Date})),3,4)),
ifelse(${Trend} = 'Year', CONCAT('FY-',toString(EXTRACT('YYYY',{Order Date}))),null)))

Which yields the following result:

image
image957×694 56.2 KB

Does this provide a solution?

Kind regards,
Andrew

1 Like