This may have already been asked previously, but my search on existing Q&A did not get me a case that was close to what I am looking for.
I want my user to select Start date of the reporting week.
Example: 19-Aug-2024
I have two calculated fields that will give me the From and To Dates to filter the data
Example:
From Date: 22-Jul-2024 Beginning of week 4 weeks prior
To Date: 25-Aug-2024 End of Reporting week
I want to filter my date field in the dataset using the From and To Date calculated fields. That does not seem to be possible as I can use only a parameter to dynamically filter data.
I can use a calculated field as a default for a parameter either, which I thought could be a workaround.
Hello Giridhar.Prabhu you’re right, in Amazon QuickSight, you can’t directly use calculated fields to filter the data. However, you can use a parameter to achieve the desired functionality.
Here’s how you can approach this:
**Create a Parameter for the Start Date of the Reporting Week:**
In the QuickSight analysis, go to the "Parameters" pane.
Click on "Create parameter" and give it a name, e.g., "Start Date of Reporting Week".
Set the data type to "Date".
You can optionally set a default value for the parameter, which could be the current date or a specific date.
**Create the Calculated Fields for From and To Dates:**
In the "Fields" pane, create the following calculated fields:
From Date: DATE(YEAR([Start Date of Reporting Week]), MONTH([Start Date of Reporting Week]), 1) - INTERVAL 4 WEEK
To Date: DATE(YEAR([Start Date of Reporting Week]), MONTH([Start Date of Reporting Week]), 1) + INTERVAL 4 WEEK - INTERVAL 1 DAY
These calculated fields will use the "Start Date of Reporting Week" parameter to determine the From and To dates.
**Filter the Date Field in the Dataset:**
In the "Filters" pane, add a new filter for the date field in your dataset.
Select the "From Date" and "To Date" calculated fields as the filter values.
**Create the Pivot Table:**
In the analysis, add a new visual and choose "Pivot table" as the visual type.
In the "Fields" pane, drag the following fields to the respective areas:
Rows: "Effective Month", "Effective Year"
Values:
Sum of "Amount" where "Status" = "Donation" (as "Sum of Total Donations")
Sum of "Amount" where "Status" = "Refund" (as "Sum of Total Refunds")
**Customize the Pivot Table:**
In the "Visual properties" pane, you can adjust the formatting, font, and other settings to match your desired output.
You can also add conditional formatting or other visual enhancements to make the data more intuitive.
**Save and Share the Analysis:**
Once you're satisfied with the pivot table, save the analysis.
You can then share the analysis with other users or embed it in a dashboard or application.
When users interact with the analysis, they will be able to select the “Start Date of Reporting Week” parameter, and the pivot table will automatically update to display the total donations and refunds for the corresponding date range.
This approach allows you to use a parameter to dynamically filter the data, which is the recommended way in Amazon QuickSight, as calculated fields cannot be directly used for filtering.
I am not sure I understood the approach completely. Based on the steps it appears From and To Date calculated fields are created in the analysis, but are to be used as filters in the dataset.
How would that work? Are the calculated fields created in analysis available in the dataset?
Additionally, putting the filter in the dataset restricts the ability to have all data in the dataset, but only filter and see what is needed by the user.
I found the following case as an answer to my question.
I did the following:
Created a Parameter Reporting Week and added that to the Control
Created two calculated fields From and To Date to derive my date range
Created an additional Calculated field which looks at the dataset field and the date range and returns a boolean 1 or 0 as a way to enable me to use that as a Filter for the visual
Used the Date in Range calculated field to Filter the visual.
