Hello,
It would be helpful to have ability to dynamically format date field based on parameter value in QuickSight.
In our case we have Period parameter (with values: Month, Quarter, Week, Year), so it would be good to format date in charts in the following way:
- Month - “MMM yyyy” (e.g. Jan 2024)
- Quarter - “Q[1,2,3,4] yyyy” (e.g. Q1 2024)
- Week - “Week [1,2,3, …, 52] - yyyy” (e.g. Week 2 - 2024)
- Year - “yyyy” (e.g. 2024)
Above formatting should not break down “periodOverPeriodDifference” and “periodOverPeriodPercentDifference”.
We have tried to create calculated field with:
- using “extract” function with “ifelse”, but it has drawbacks:
- “extract” function doesn’t allow to get week from data
- result is string type, what causes that “periodOverPeriodDifference” and “periodOverPeriodPercentDifference” functions don’t work.
- there are problems with sorting - results can be sorted alphanumerically, but not chronologically due to string type
- using “formatDate” function with “ifelse”:
- “formatDate” doesn’t support formats like “yyyy”, “MMM yyyy”, week numbers and quarters (Supported date formats - Amazon QuickSight)
- result is string type, what causes that “periodOverPeriodDifference” and “periodOverPeriodPercentDifference” functions don’t work
- problems with sorting occur as well
Thread, which contains more details: Dynamic formatting values based on parameter
Best Regards,
Piotr