How to show Quarter as Q1-year, Q2-year, values on drop down

[Shadow]

I’m having a drop down with Daily, Weekly, Monthly, Quarterly, Yearly.. but when i select quarterly, i want the values to be display as Q1-2025, Q2-2025, Could you please give me the step by step tips as per my requirement.

[lary_andr]

Hello @Shadow Welcome to community!
Hope this message finds you well!

If I understands you well, you need a selecting “Quarterly” from a dropdown displays values like “Q1-2025”, and I think that you can utilise parameters and calculated fields to solve that. Here’s my suggestion:

1. Define a Parameter for the Dropdown:

   • Create a new parameter with the following settings:
     • Name: TimePeriod
     • Data type: String
     • Values: Include options such as Daily, Weekly, Monthly, Quarterly, Yearly.

2. Set Up a Control for the Parameter:

   • Go to the Controls tab and add a new control.
   • Configure it as follows:
     • Control type: Dropdown
     • Parameter: TimePeriod
     • Values: Link it to the parameter you created.

3. Create a Calculated Field for Quarterly Values:

   • In the Analysis tab, add a calculated field with the following logic:

     ifelse(
       ${TimePeriod} = 'Quarterly',
       concat('Q', trunc((extract("MM", {date_field}) - 1) / 3) + 1, '-', extract("YYYY", {date_field})),
       NULL
     )
     

   • Replace {date_field} with the appropriate date field from your dataset.

4. Incorporate the Calculated Field into Your Visual:

   • Add this calculated field to your chart or table.
   • Ensure the visual is filtered to display these values only when TimePeriod is set to Quarterly.

5. Validate the Setup:

   • In preview mode, test the dropdown selections to confirm that quarterly values like “Q1-2025” appear correctly when “Quarterly” is chosen.

Please, tell me if it helps you!

[Shadow]

Hey @lary_andr Thanks for your reply, but when i try the same am getting error when i add the calculated field, could you please help me on that

and currently am using this code on the calculated field: which is taken from one of your blog

ifelse(

${granularity}='Daily',truncDate("DD",{Adjusted Order Date}),
${granularity}='Weekly',truncDate("WK",{Adjusted Order Date}),
${granularity}='Monthly',truncDate("MM",{Adjusted Order Date}),
${granularity}='Quarterly',truncDate("Q",{Adjusted Order Date}),
truncDate("YYYY",{Adjusted Order Date}))

Could you pls modify this code${granularity}='Quarterly',truncDate("Q",{Adjusted Order Date}), for quarterly as Q1-YYYY

[lary_andr]

Hey @Shadow
In this case, a solution could be something like:

ifelse(
  ${granularity}='Daily', truncDate("DD", {Adjusted Order Date}),
  ${granularity}='Weekly', truncDate("WK", {Adjusted Order Date}),
  ${granularity}='Monthly', truncDate("MM", {Adjusted Order Date}),
  ${granularity}='Quarterly', 
    concat('Q', trunc((extract("MM", {Adjusted Order Date}) - 1) / 3) + 1, '-', extract("YYYY", {Adjusted Order Date})),
  truncDate("YYYY", {Adjusted Order Date})
)

Please, tell me if it helps

[Shadow]

HI @lary_andr

I’ve tried the same but still am getting error while saving the calculated field. Could you pls help on this?

[Gajula]

Same issue i am facing not working the solution provided

[msnehali]

Hi All,

For the if-else you may need to use same return data type for all the clauses: As mentioned in the document.

ifelse returns a value of the same data type as the values in then-expression . All data returned then and else expressions must be of the same data type or be converted to the same data type.

[+] Ifelse - Amazon QuickSight

---

Regarding the displaying of Quarter:

Defining the Quarter like mentioned here:
Custom quarter data - #9 by Neeraj

This helps in getting value like below screenshot:

concat (‘Q’ , toString( extract ( ‘Q’, addDateTime(1, ‘MM’, truncDate(‘MM’, {Order Date})))) , ’ ', toString(extract(‘YYYY’, addDateTime(1, ‘MM’, truncDate(‘MM’, {Order Date})) )) )

However this will be treated as “String” hence Regular “Date” datatype like sorting (ASC/DESC) may not be possible.

[Shadow]

Hi @msnehali I’ve tried changing the formatDate without changing toString instead of 2023-01-01 00:00:00.000 to Jan 1, 2023 but its not changing as expected, Need your suggestions on this..

[msnehali]

Hi @Shadow

I used this:

ifelse(
${Granularity}='Quarter',{Concat week} ,
${Granularity}='Month',formatDate(truncDate('MM', {Order Date}), 'dd-MMM-yyyy')
,${Granularity}='Week',formatDate(truncDate('WK', {Order Date}), 'dd-MMM-yyyy'),
formatDate(truncDate('DD', {Order Date}), 'dd-MMM-yyyy'))

I get date as below:

[Shadow]

Hi @msnehali I tried the same but getting error on the “concat week”, its throwing error on this! how to fix this?

[msnehali]

@Shadow I think the Calculated Field in your Analysis is different and not “{Concat week}” as in my test account, please replace it with the Calculated Field which is calculating the Quarter Value.

Quarter Calculation:

concat (‘Q’ , toString( extract ( ‘Q’, addDateTime(1, ‘MM’, truncDate(‘MM’, {Order Date})))) , ’ ', toString(extract(‘YYYY’, addDateTime(1, ‘MM’, truncDate(‘MM’, {Order Date})) )) )

[Shadow]

Hi @msnehali its working now but the format is not displaying correctly if i select monthly, weekly(even am getting 2023 year result which am not even selecting as date range - kindly verify the select date), daily am getting this (screenshots attached below) and also i need weekly calculation with 7 days difference