Issue: I have a dashboard in Amazon QuickSight that tracks metrics on a weekly basis, with snapshots taken every Monday. I need to calculate and display both Month-over-Month (MoM) and Quarter-over-Quarter (QoQ) percentage changes.
The current issue is that when I filter the snapshot date down to a single week (Monday), the MoM and QoQ metrics go blank. I understand that this happens because the Period-over-Period (PoP) function requires multiple time periods to compare. However, I need a solution that allows me to display MoM and QoQ changes even when filtering down to a single snapshot date.
Requirements:
- The MoM calculation should compare the current week’s value to the value from 4 weeks ago.
- The QoQ calculation should compare the current week’s value to the value from 12 weeks ago.
- The dashboard should retain the functionality of filtering to a single snapshot date while still displaying MoM and QoQ metrics.
Current Setup:
- Snapshot dates are every Monday.
- Calculations for MoM and QoQ are set up using the
periodOverPeriodDifferencefunction. - I have tried filtering the visual by a single snapshot date, but this causes the MoM and QoQ calculations to return blank values.
What I Need:
- A method to ensure that MoM and QoQ calculations are displayed even when a single snapshot date is selected.
- If necessary, guidance on whether this can be achieved through QuickSight’s built-in functionality or if a custom SQL query is needed to handle the comparison across dates.
Any suggestions or workarounds would be appreciated.