Hi @Rodger, it seems you’re encountering challenges with using parameters within calculated fields. I’ll try to provide some highlevel guidance to help you get started, the syntax is pseudocode, please check the documentation and use the Amazon Q to help derive the correct syntax.
Steps to Achieve the Desired Output:
- Create Parameters:
- Ensure you have created two parameters:
EndOfLast2Weeks and EndPreviousWeek.
- Create Calculated Fields:
- You need to create two separate calculated fields that will use these parameters. For weeks, your logic will be similar but adjusted for weekly granularity.
Calculated Field for Last 2 Weeks:
Pseudocode, verify syntax.
ifelse(
{Date} >= dateAdd('WK', -2, truncDate('WK', now())) AND
{Date} <= dateAdd('WK', -1, truncDate('WK', now())),
{Count},
null
)
Calculated Field for Previous Week:
Pseudocode, verify syntax.
ifelse(
{Date} >= dateAdd('WK', -1, truncDate('WK', now())) AND
{Date} < truncDate('WK', now()),
{Count},
null
)
- Create Aggregated Calculated Fields:
- Now, create aggregated calculated fields using
sum or average as needed.
Aggregated Field for Last 2 Weeks:
Pseudocode, verify syntax.
sum(
ifelse(
{Date} >= dateAdd('WK', -2, truncDate('WK', now())) AND
{Date} <= dateAdd('WK', -1, truncDate('WK', now())),
{Count},
null
)
)
Aggregated Field for Previous Week:
Pseudocode, verify syntax.
sum(
ifelse(
{Date} >= dateAdd('WK', -1, truncDate('WK', now())) AND
{Date} < truncDate('WK', now()),
{Count},
null
)
)
- Difference Calculation:
- Create a final calculated field to calculate the difference between these two aggregated fields.
Difference Field:
Pseudocode, verify syntax.
sum(
ifelse(
{Date} >= dateAdd('WK', -2, truncDate('WK', now())) AND
{Date} <= dateAdd('WK', -1, truncDate('WK', now())),
{Count},
null
)
)
-
sum(
ifelse(
{Date} >= dateAdd('WK', -1, truncDate('WK', now())) AND
{Date} < truncDate('WK', now()),
{Count},
null
)
)
- Use Parameters in Filters:
- Ensure your dataset is correctly filtered using the created parameters. These parameters should dynamically adjust your date ranges.
Verification and Troubleshooting:
- Verify Parameter Values: Ensure that the parameter values are set correctly and match the expected date ranges.
- Check Date Field Formatting: Ensure your date fields in the dataset are properly formatted as dates.
- Debug Step-by-Step: Test each calculated field individually to ensure they return the expected values before combining them.
- Data Availability: Ensure that your data for the specified weeks actually exists in the dataset. Sometimes, empty results occur due to no data matching the date range.
If these steps do not resolve your issue, try using custom SQL or pre-processing at the data source.
Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!