Previous Year Total values (at Quarterly Level) in Quicksight

[chabbils]

Hi Team, in the attached screenshot, I’ve Units (CY), Total Units (CY) and Units (PY). I want to create a calculated field for Total Units (PY) (having a value of 334.1M against each record as per attached screenshot). I tried creating the same but not getting the right values. Can you please help me with this? Below is how my calculated fields look like:

• Total Units (CY) : sum(Units,[{Reporting Date},{Reporting Grain},Marketplace])
• Units (PY) : periodOverPeriodLastValue(sum(Units), {Reporting Date}, YEAR, 1)

[Max]

@chabbils

sum(Units,[{Reporting Date},{Reporting Grain},Marketplace])

Do you need to do this to your py as well?

[chabbils]

@Max

sum(Units,[{Reporting Date},{Reporting Grain},Marketplace])

@Max I already tried it. It shows attached error.

[Max]

can you try using a sumOver with a pre_agg?

sumOver(Units,[{Reporting Date},{Reporting Grain},Marketplace],PRE_AGG)

[chabbils]

@Max I tried but not getting the required result/value. It’s giving me Negative values. Screenshot attached. Please help.

[Max]

What is your exact calculation now?

[chabbils]

@Max see attached screenshot.

[Max]

That’s your calculation for Total Units (Previous Year)? You must have negative units.

[chabbils]

@Max Yes, I created this calculation based on the formula you mentioned above but my ask is different. I want to create a calculated field under Total Units (PY) having a value of 334.1M against each record as per attached screenshot, the way I’ve 471.9M under Total Units (CY) against each record.

[apjvinod]

@chabbils I assume you want to calculate total units for the same quarter last year in the pivot table .

Please use the below expression to calculate it , ensure to keep the same level for the current year & previous year measure .

periodOverPeriodLastValue(sumover(sum({Units}),[{Reporting Date}]),{Reporting Date},QUARTER,4)

I have tried in a sample dataset and it works . Please see screenshot

Did my suggestion help you in resolving your query? If yes, would request you to mark the post as “Solution”.
This will help the community to find guidance and answers to similar question.

Thanks
VInod

[chabbils]

@apjvinod

periodOverPeriodLastValue(sumover(sum({Units}),[{Reporting Date}]),{Reporting Date},QUARTER,4)

Thanks @apjvinod , i was able to implement it.