Filter control displaying different values to those in the data

Question & Answer
, , ,
1

Hi,

I’m running into a pretty weird bug, and I think it is because I have changed the start of the week in my analysis to begin on Monday rather than Sunday.

I have created a calculated field “Week commencing” that tells me what week the data I have is for and formatted in a specific way:

formatDate(truncDate('WK', {date}), 'd-MMM-yyyy')

This field then gives me values of ‘22-Jun-2024’ for last week and ‘29-Jun-2024’ for the current week. However, when I apply a filter on this field and add it to the top of the dashboard, the values displayed in the filter control dropdown are ‘21-Jun-2024’ and ‘28-Jun-2024’. So when I select one of these values, no data is displayed in my table as the values in the filter aren’t the same as in my data.

2

Hi @Liam

Try creating the calculated field with the below formula:

formatDate(addDateTime(1,‘DD’,truncDate(‘WK’, {Order Date})), ‘dd-MMM-yyyy’)

create a filter and control with this newly created calculated field.

Hope this resolves your issue.

Thanks

1 Like
3

Hi,

That doesn’t solve my issue, as the week commencing filter still displays ‘21-Jun-2024’ and ‘28-Jun-2024’ as the options in the filter control dropdown at the top of my analysis.

Thanks,
Liam

4

Hi,
The filter control dropdown needs to be created with the newly created calculated field.
image.

Thanks

5

Hi,

I have done that, this screenshot clearly shows that the filter has been added on the calculated field and the control has been added to the top of the page:

filter enabled

Then this screenshot is of the control itself, clearly the values being displayed in the control don’t align with the actual data for some reason:

control options

Thanks,
Liam

6

Hi,
Perhaps, it should be working. Can you also check if this calculation has been used in the week commencing field.

formatDate(addDateTime(1,‘DD’,truncDate(‘WK’, {Order Date})), ‘dd-MMM-yyyy’)

And also check if the values are refreshed in the filter pane.

image
image230×682 16.7 KB

Thanks.

1 Like
7

Hi @liam, @Yamini_Baskar_24 ,

This seems to be a bug with the filter control value generation. I have created a ticket for this issue - Internal link. If you want to keep track of updates, please submit a support ticket and reference the above link in there.

Here are the steps to open a support case. If your company has someone who manages your AWS account, you might not have direct access to AWS Support and will need to raise an internal ticket to your IT team or whomever manages your AWS account. They should be able to open an AWS Support case on your behalf.

I wouldn’t recommend any workaround with addDateTime function as that will start to fail at a later stage when this bug is fixed.

Regards,
Arun Santhosh
Pr QuickSight SA