LAG Function Grabbing Values Across Rows Instead of Across Columns (Weeks) — Need Dynamic Partition Solution

Q&A
, ,
1

Hi everyone,

I’m working on a QuickSight pivot table and running into an issue with the LAG function. I’d appreciate any guidance!

Setup

  • Columns: Aggregated by Week (e.g., 2026-02, 2026-03)
  • Rows: Region and State
  • Metrics: Current Week Metric 1, Last Week Metric 1
    image
    image972×413 24.3 KB

Goal

I want Last Week Metric 1 to grab the Current Week Metric 1 value from the previous week for the same Region and State.

For example:

  • Washington’s “Last Week Metric 1” for 2026-03 should equal Washington’s “Current Week Metric 1” from 2026-02 (-8.01%)
  • Instead, it’s currently grabbing values from different states within the same week(refer to the blue, yellow, green highlights for reference)

Current Calculations

Current Week Metric 1:

RAP - sumOver(
    sum({Yes Response}),
    [{Response Week}]
) 
/ 
sumOver(
    sum({Yes Response}) + sum({No Response}),
    [{Response Week}]
)

Last Week Metric 1 (using LAG):

lag(RAP, [{Response Week} ASC], 1) -
lag(
    sumOver(
        sum({Yes Response}),
        [{Response Week}]
    ) 
    / 
    sumOver(
        sum({Yes Response}) + sum({No Response}),
        [{Response Week}]
    ),
    [{Response Week} ASC],
    1
)

The Problem

The LAG function is grabbing values within the same week across different Region/State rows instead of grabbing values across weeks for the same Region/State.

Concern — Dynamic Partitioning

I understand that adding partition fields (Region, State) would work for this specific visual:

lag(
    your_metric,
    [{Response Week} ASC],
    1,
    [{Region}, {State}]
)

However, my dashboard has multiple visualizations with different row aggregations:

  • Some visuals use Region + State
  • Some use other dimension combinations
  • Some are controlled by parameters that change the row aggregation dynamically

If I hardcode partition fields, I would need to create a customized calculation for each combination, which is not scalable or maintainable.

Questions

  1. Is there a way to create one universal LAG calculation that dynamically adapts to whatever dimensions are used in the row aggregation?
  2. Can QuickSight automatically detect the row-level grouping and apply LAG across columns (weeks) accordingly?
  3. Is there a workaround using PRE_AGG, PRE_FILTER, or other level-aware functions?

Any suggestions or best practices for handling this scenario would be greatly appreciated!

Thanks in advance!

2

Hi @yuchen1,

Nice seeing you back in the Quick Community! I can definitely understand your frustration for this particular use case. I am not sure if this achievable due to the multiple dimensions being aggregated. However, would it be possible if you can send me a Arena view of your analysis (sensitive info removed)? I would still like to try in case and see if there is a workaround to your issue.

Thank you!

3

Hi @yuchen1

It’s been a while since we last heard from you. If you have any further questions, please let us know how we can assist you.

If we don’t hear back within the next 3 business days, we’ll proceed with close/archive this topic.

Thank you!

4

Hi @yuchen1

Since we have not heard back from you, I’ll go ahead and close/archive this topic. However, if you have any additional questions, feel free to create a new topic in the community and link this discussion for relevant information.

Thank you!