Custom quarter date format

[Bonnie213]

I have a dashboard with parameters and calculated field that makes it to be able to aggregated to daily/weekly/monthly/quarterly level, here is the calculated field
Time Granularity =
ifelse(
${TimeGranularity} = ‘Daily’,truncDate(‘DD’, {reporting_period}),
${TimeGranularity} = ‘WBR’, truncDate(‘WK’, {reporting_period}),
${TimeGranularity} = ‘MBR’, truncDate(‘MM’, {reporting_period}),
${TimeGranularity} = ‘QBR’, truncDate(‘Q’, {reporting_period}),
{reporting_period}
)

Buw now I want the Quarterly level (QBR) to have something like “Q3 2025” instead of a start date of a quarter, I tried using the custom dates feature on quicksight, but since I am changing the custom field of Time Granularity(the calculated field), all levels will change accordingly.

I also tried to just create a calculated field for quarter,
quarter =
concat(
toString(extract(“YYYY”, {reporting_period})),
" Q",
toString(ceil(extract(“MM”, {reporting_period}) / 3))
)
But when I tried to put this thing into the Time Granularity calculated field like this:
ifelse(
${TimeGranularity} = ‘Daily’,truncDate(‘DD’, {reporting_period}),
${TimeGranularity} = ‘WBR’, truncDate(‘WK’, {reporting_period}),
${TimeGranularity} = ‘MBR’, truncDate(‘MM’, {reporting_period}),
${TimeGranularity} = ‘QBR’, {Quarter},
{reporting_period}
)
It also gives me an error, I am assuming this is because the data type is different from each level.

I also tried to convert all level to string but still didn’t work.
Any suggestions?

[apjvinod]

Hi @Bonnie213

Could you please go thru this article and it explains how to do different date formatting dynamically .

[Bonnie213]

Thank you for sharing the article! I tried the same method in the article, but it didn’t work for me because I have a Period over Period column(calculated field) that calculates the difference between each day/week/month/quarter. If change the date format to a string, that calculated field is not going to work for me anymore because it depends on the date type to do the periodOverPeriodDifference(). Is there any workaround you can think off?

[prantika_sinha]

Hi @Bonnie213 ,

You are correct, it is because of the data type difference. Can you try using formatDate function to format the date returns as string also?

formatDate - Amazon QuickSight

If you tried this already and having an error, kindly share the calculation you tried to debug better.

And you can keep one field in date format to proceed with the period over period calculations and one string for adding it in visual. That should not cause this conflict further.

Thanks,
Prantika

[Bonnie213]

I’ll have to add both field(date and string) to the pivot table so my dashboard can be able to switch to daily/weekly/monthly/quarterly level. I planed to use the string one for displaying and the date one for controlling the time granularity(and I can hide this field in the dashboard) But this method didn’t work. Adding both field still makes the period over period difference column not working.

It supposed to be like this with the date column only:

But if i add the string column, the period over period difference won’t work