Date, which is ‘Date picker - range’ type and is defined for Departure calculated field
PeriodEnding (parameter), which is Dropdown with 4 values: Month, Quarter, Week, Year
Departure field is calculated according to the formula, which truncates source field to periods, which are selected in filter:
ifelse(
${PeriodEnding}=‘Day’,truncDate(“DD”,{DEPARTURE_DATE}),
${PeriodEnding}=‘Week’,truncDate(“WK”, {DEPARTURE_DATE}),
${PeriodEnding}=‘Month’,truncDate(“MM”,{DEPARTURE_DATE}),
${PeriodEnding}=‘Quarter’,truncDate(“Q”,{DEPARTURE_DATE}),
truncDate(“YYYY”,{DEPARTURE_DATE}))
The Departure field, which is currently used in analysis (e.g. in Tables and Pivot Tables), is displayed in full format (days, months and years).
However, I would like to adjust the display format in dependency of PeriodEnding parameter value - e.g. when Quarter is selected then I would like to show the appropriate quarter (Q1 2024, Q2 2024, Q3 2024 or Q4 2024) instead of full date (with days, month and years).
In Quicksight is it possible to do such dynamic formatting, that depends on parameter value?
If it is not possible to dynamically format values in Tables/Pivot Tables, maybe do you have some idea how to achieve it in another way?
Yes, I have tried formatDate funtion, but if I see correctly it supports only limited list of date formats - Supported date formats - Amazon QuickSight
All formats, which are supported by formatDate need to contain day, month and year, whereas in my case I would like sometimes skip days or months (e.g. when PeriodEnding parameter has value Year I would like to show only year).
I tried to use extract function and convert it to string value. I created new calculated field according to the simple formula:
ifelse(
${PeriodEnding}=‘Day’,formatDate({DEPARTURE_DATE}, “dd-MM-yyyy”),
toString(extract(‘YYYY’, {DEPARTURE_DATE})))
I used this calculated field in Table or Pivot table in Group By section and removed Departure field (which was calculated using formula, which I put in the previous comment) from table. This approach unfortunately doesn’t work correctly with periodOverPeriodPercentDifference function (as date argument in this function I put Departure field, which is date type). Field, which should show value for period over period difference is blank.
I have tried to keep departure date together with calculated field, which casts date to string in appropriate format (PM Test field in screenshot). In this approach, rows with date contain correctly calculated YoY (using periodOverPeriodDifference) while rows with casted date to string (PM Test) don’t contain any value for YoY. Other functions such as sum or average seem to work correctly for both dimensions.
This approach also causes that rows are duplicated (e.g. Jan 1, 2023 and 2023). I would prefer to have single row for year (or for other period selected from Period) with correct formatting (e.g. 2023).
you can click on the three dots on the right sight of the Depature Date pill (under rows) to open a menu where you can change the display formatting without casting it to string. This could help you to reduce the duplicated lines.
This helps to reduce duplicated lines, but if I set “Format: 2024” then it displays always year, even after changing PeriodEnding parameter value (in screenshots visible as Period filter) to “Month”.
I would like to do such formatting in dynamic way, so when “Month” is selected in Period then formatting should be automatically adjusted. Is it supported by QuickSight?
Unfortunately, it doesn’t fully solve my question, because as you noticed, results cannot be sorted by date.
There is another drawback - function periodOverPeriodDifference doesn’t work because in this approach, we change field type from date to string.
Steps, which I executed:
Adding “PM Formatted Date” field with your function
Ideally it would be to have field of date type (sorting and periodOverPeriodDifference function would work in such case) and dynamically (in dependency of parameter Period) define formatting of this field, but I don’t know if it is possible to achieve it in QuickSight.
One last idea regarding the sorting topic. You can change from MM YYYY format to YYYY MM and add leading zeros to your month for month Jan - Sep. So an alphabetic sorting is the same as it would look like with a date.
I think the other features you mentioned are not possible right now. You can create a feature-request for it.
