I have a big dataset that I am performing aggregations before bringing in. Numbers are pre-calculated for each cadence (day, week, month, quarter, year) due to the existence of distinct counts that I cannot simply calculate for a day and sum up.
So the general schemais like:
day, guest_day, week_start_dt, week_guest, month_start_dt, month_guest…etc.
The week data in the 7 days will be repeated same for month and quarter and year.
On the dashboard, I am allowing users to select cadence thru controls and I control the x-axis with a calculation field with switch. The default granularity for the x-axis is day.
My question is when user switches to month or quarter, on x-axis it will simply show the date of quarter_start_dt, but normally when drilling up/down it will be Q1’2024 or Q2’2024. Is there any way for me to control the format like that? The last solution I can think of is using rules to create 4 replicas…but I want to ask the community here before going that route.
I ran into a similar issue when trying to show metrics over different periods. The way I did initally was to truncDate everything but I did not have a transformed date field.
I would recommend trying two things: 1) Create a conditional statement for your x-axis if you haven’t already so that it changes with a user selection from a parameter control OR 2) you will have to create the 4 visuals and layer them in free form layout.
hey @duncan!
Thank you for the reply! I do already have a conditional statement for the x-axis. The issue is for higher level cadence data, for example, for quarter data, I want x-axis to be in format like Q1’2023, but I only get one overall format selection for the x-axis. So right now, quarter will show Jan 1st, 2023. This format is what I want for weekly data, but when changed to quarterly, I would prefer Q1’2023 instead.
If I understand you correctly, that is not possible from the UI in QuickSight.
It might get overly complicated, but you could add that logic to a group by field so that if your x-axis date selection is in Quarters and the your metric is within the dates from Jan - March for example, that it falls into “Q1 2023”.
I will mark this as a feature request for the QuickSight team!
