Changing x-axis group and sort with parameter

I have an example below of 2 bar charts. One is grouped by a fiscal year (string field) and the other is grouped by a fiscal quarter (calculated string field).

The first chart is sorted by Fiscal Year along the x axis. This sorts correctly as a string and puts the “dates” in order.

The second chart wouldn’t work to sort by the fiscal quarter string field. So I am choosing to sort by a different field to get this to be in the correct order.

For these types of charts, the desire would be to have one chart and have a parameter control where the user can choose if they want to see the data by year, quarter, month, etc.

I know how to create this parameter control, create a calculated field that checks it and returns either my FiscalYear field or my FiscalQuarterWithYear field, and then I put that calculated field into my X-axis group.

My question is, how do I also control/change the sort in this scenario?

1 Like

@CoreyLeichty , for Fiscal Quarter, could we do YYYY Q#, for example, 2022 Q1, 2021 Q4 and so on. And then, your sort should be correct based on the Fiscal Quarter column.

Hi @CoreyLeichty , Could you share the formula you used for calculating the x-axis?

I am getting syntax error. Not sure where exactly I am making the mistake.

Here is my formula:
ifelse(

${Granularity} = “Year”, extract(‘YYYY’, stage_ts),

${Granularity} = “Quarter”, extract(‘Q’, stage_ts),

${Granularity} = “Month”, extract(‘MM’, stage_ts),

${Granularity} = “Week”, extract(‘WD’, stage_ts),

${Granularity} = “Day”, extract(‘DD’, stage_ts),

“Null”)