I want to compare two values within same column based on the partition in other column.
In the image, both years 2022 & 2023 are in same column, i want to calculate the difference of the value at Month level (Jan22 vs Jan23 and so on) and plot the diff in another row as shown in image
Hello @Geethapriya_U, welcome to the QuickSight community! Would you be able to set up a demo version of this analysis in QuickSight Arena and link it in your response? That would allow me to test some options that you could implement in your dashboard.
Depending on how the data is set up, you may need to utilize a calculated field like lag to bring the 2 comparison values on a single row. You might also be able to build a calculation using LAC-W aggregations to create the difference field.
Diff = sumOver(ifelse({Year} = '2022', {value}, NULL), [{month}], PRE_AGG) - sumOver(ifelse({Year} = '2023', {value}, NULL), [{month}], PRE_AGG)
Since your date related fields seem to be broken up, I am assuming they are string values rather than actual datetime fields. That removes the option to use periodOverPeriod calculations in QuickSight. With a little more information, I can help you find a different way to make this work.
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!