Lookup Values from Previous Year

I’m trying to create a calculated row using values from rows A, B, and C. The calculation should be A (current year) / B (prior year) * C (prior year). Is there a way to look up the prior year values for the calculation?

Hi @QuickSightLover Welcome to the QuickSight community :slight_smile:

Does this answer your question: Is there a way to add a 'Row' calculated field? ?

Hi @QuickSightLover

Not sure you can do it in the same (pivot) table but you can definitely calculate it by creating 3 calculated fields:

A → ifelse(date=2023 and Category=‘A’, measure, null)
B → ifelse(date=2023 and Category=‘B’, measure, null)
C → ifelse(date=2023 and Category=‘C’, measure, null)

then create another calculated field to do the calculation A / B+C

In the example below I have months in columns and different topics in rows. Just like in your use case, I am interested only in one date (the month of March). Using parameters I can change the numerator and denominator of a division that shows up at the bottom.

This is how the numerator looks like: ifelse(topic=${Param1} and extract(‘MM’, date)= 3, {survey_response}, null)

Thanks for your example. I think your use case only calculates for a specific month. Instead, I’m trying to calculate an entire row using {survey_response} from different months.

I reproduced your example in excel, but created an extra row call “Calculated” which sums all topics for any given month except for Customer Service which is taking {survey response} from prior month. Is there a way to do this? Can we leverage Lag? If so, how would we incorporate this?


Hi @QuickSightLover

You can use periodOverPeriodLastValue in combination with aggregations.

periodOverPeriodLastValue(sum({arrival_timestamp_int}),{arrival_timestamp},MONTH,1) + sum({arrival_timestamp_int})

You would just need to separate this out with if statements depending on your service_type