Please suggest formula to achieve this result in Quicksight?

How to achieve this result in Quicksight in the attached sheet ?


?

in the attached sheet the march period output is march trade month minus feb trade month ?..
similarly for other months- let’s say for jan month it will be jan 2025 trade month minus dec 2024 trade month , april 2025 it will be april trade month minus march trade month

Hi @bhumika18s

You may be able to get this working using the lag function to get the value from the previous month.

See QuickSight documentation with example in the post referenced below

Regards,
Giri

thanks for the response, par i don’t think lag will work as data won’t be present in a certain order for trade month column

Hello @bhumika18s & @Giridhar.Prabhu

How will the rows be ordered in the dataset? Lag would work if the trade month column is sorted by month. Are the rows sorted by another dimension?

Hi @duncan , @bhumika18s

The Lag can be used in the analysis. I don’t think it can be part of the dataset definition since that requires data to be in certain order.

Since the purpose of the lag is to get the previous value you can specify the sort order before the lag is evaluated.

Give it a try on a sample set of data to see if it meets your criteria.

Regards,
Giri

1 Like

Hi @bhumika18s,
It’s been awhile since we last heard from you on this thread, did you have any additional questions regarding your initial post or did the solution provided above help with your case?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you

Hi @bhumika18s,
Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community.

Thank you