I have what I would think is a relatively simple issue, but I’m finding it hard to correct it in my dashbaord.
I have a dashboard which has KPI cards at the top and then some bar chart visuals below showing some sales numbers.
The dashboard is a prior day summary, so it should always default to the prior day’s information
I only have business day information (i.e. Mon-Fri) in my underlying dataset.
I have a parameter at the top of page which is linked to a date column in my dataset, which drives the data in the visuals for the KPI and Bar charts. I have defaulted this parameter to be Relative = “Yesteryday”.
This issue I’m having is that when users log in on Monday morning, they are seeing the date picker default to “yesterday” which is a Sunday - so no data shows in the corresponding visuals, as the date being passed from the parameter is a Sunday, all other visuals do not render data, as there is nothing corresponding to it in the dataset.
Does anyone have any ideas how I can fix this? Perhaps, get it to default to “prior business day”.
I’ve tried a calculated field, which checks to see what day it is, then shift the date returned to prior business day, but unfortinately I cannot use this to drive the parameter values.
Calculated Field A = You already determine which date it is
Calculated Field B = if (dataset datefile = Calculated Field A, 1, 0)
In your visual add a filter on Calculated Field B where value equals = 1
This has worked for me in cases where the user selects a start of week and I determine 5 weeks backwards from there to determine the date range. So I presume it should work for you as well.
I’m not sure this fixes my problem, as the parameter will always default to “yesterday”. So the real issue I have is that there is no flexibility for “previous business day”. Whilst there is functionality for previous n weeks/months/seconds/minutes etc., there is nothing for previous business day.
I think this is core functionality for businesses or datasets that only have Monday-Friday data.
So there is no real way to set the parameter control up so that it will default to previous business day.
I’m interested to hear other peoples thoughts on this.
Giridhar’s approach will get the right data showing, but the date picker itself still displays Sunday on Monday mornings, which is confusing. There’s no native “previous business day” option in Quick Sight’s relative date defaults.
The way to fix the control itself is with a dynamic default parameter (DDP). You create a small direct query dataset that computes the previous business day and maps it to your users. I haven’t tested this for this exact scenario myself, but the pattern is well-documented for computed date defaults and the mechanics should hold. The SQL would look something like:
SELECT
qs_user.user_name,
CASE
WHEN EXTRACT(DOW FROM CURRENT_DATE) = 1 THEN CURRENT_DATE - 3
WHEN EXTRACT(DOW FROM CURRENT_DATE) = 0 THEN CURRENT_DATE - 2
WHEN EXTRACT(DOW FROM CURRENT_DATE) = 6 THEN CURRENT_DATE - 1
ELSE CURRENT_DATE - 1
END AS previous_business_day
FROM your_user_table qs_user
(Adjust the DOW values for your database engine. The sql assumes PostgreSQL/Redshift where 0=Sunday, 6=Saturday.)
Add that dataset to your analysis, edit your date parameter, choose “Set a dynamic default”, and map the columns. On Monday the control should show Friday. Users can still override it manually. I’d go with direct query over SPICE for this dataset so CURRENT_DATE resolves fresh on each dashboard load rather than at refresh time.
The downside is that DDPs require a per-user or per-group mapping, so you do need to maintain a user list (or use a group if you’re on Enterprise edition). Also worth noting that email reports ignore dynamic defaults entirely ( Resolve Quick Suite "no data on all visuals" errors | AWS re:Post ).
Appreciate the reply here, as it is helpful information into how I may resolve my issue.
You mention “map it to your users” - I presume you mean this in the Advance Filters section when creating a parameter - is this correct?
If so, I dont beleive this will fix my problem. I have my users accessing the dashboard based on their group membership - and at this point, there is not column/field in my dataset which specifically captures the relevant groups (i.e. not RLS or CLS applied).
I feel like this should be an “out of the box” piece of functionality, as I’m pretty sure there will be other user’s who have only business day (Mon-Fri) data and they need to either display or distribute to users. I know you can set the timezone of a dashboard, so perhaps a fix here is to have some config for 5 day week or 7 day weeks (or something similar). How can we get this raised as enhancement request?
The mapping you’re thinking of isn’t in Advanced Filters. It’s in the parameter editor: click your date parameter, choose “Set a dynamic default,” and it points at a separate dataset you add to the analysis. That dataset has nothing to do with your main data, RLS, or CLS. Since you’re using group-based access (Enterprise edition), you can map by group name. Your DDP dataset is just two columns: group_name | previous_business_day, one row per group, all returning the same computed date. Add it as a direct-query dataset and the date picker itself will show Friday on Monday mornings. Setup docs here: Creating parameter defaults in Amazon Quick - Amazon Quick