how to create a quicksight calculated field that retrieves the mon
thly value of a metric from the previous month without using the periodoverperiod function (see screenshot where I would like to populate the column “Last Month Fav resp” which would be the value of the Fav responses corresponding to the “LM_Start_Date”)?
Thank you for you help!
Hello @diegodev, welcome to the QuickSight community!
If you are wanting to bring a value linked to a previous month in a row containing the current month value, you should use the lag function. Use it to target the measure you are wanting, sort it by your date field in ASC order, bring it in for every month using 1, and you could partition it further if needed. That is where you might want to add your User or Login field. I’ll link the documentation below. This will be the best way to receive your desired solution! Let me know if you have any further question.
Hello @diegodev, we haven’t heard back from you in a few days so I wanted to check in and see if my last response helped guide you to your expected result. I will mark it as the solution for now, but let me know if you have any further questions. Thank you!
Hello Dylan, thank you for the support. Will the lag function work if I don’t want to display the value of the previous month in the view? e.g. the QS user filters only on current month but wants to visualize the MoM trend thanks to the lag function
Hello @diegodev, you will likely need to have the field present in the field well of your visual, but since you are using a pivot table you can hide it from view. I’ll link documentation on that below:
