How to Compare the latest month's value with oldest month's Values in PeriodOverPeriod Computation of Insights

Problem Statement*: Am trying to build automated insights on a data set where we have month on month sales data with us, and am trying to show increase/decrease in sales based on the selected months in Filter.

For example if May,June,July is selected in the filter, then the Period over Period computation should show insights based on July and May sales( ie. latest and oldest month).

Issues we are facing :

  1. Irrespective of the filters selected, insights are always shown based on the latest month selected and month previous to the latest. PFB screenshot for the same.
    PFA for better understanding in screenshot 1.

  2. And insights are going for a toss if months selected in filter are not in continuation. For example when May and July are selected in the filter, we are expecting Quicksight to give the insight narrative based on sales of July and May,
    PFA for better understanding in screenshot 2.

Hello @Vivek_Kundu, welcome to the QuickSight Community! I’m not sure if you have discovered a solution to this yet on your own but I thought this was a really cool question and wanted to follow-up.

Something you could try would be to create a calculated field that takes a dateDiff function to get the date difference by Month between the 2 months being selected in your filter.
dateDiff(${firstMonth}, ${secondMonth}, "MM")

This may require you to alter your control creation, depending on how you did it previously, to be built by 2 date parameters that would allow a user to select the first month and second month.

  • In order to use parameters within a calculated field you will need to ensure that a default value is set for each, which will also guarantee that a filter of some kind is applied to the visual on load!

Once you have the dateDiff field, then you can create your periodOverPeriod difference function and subsitute the offset value with your dateDiff calculated field so this can be determined dynamically.

Now try recreating the insight with this new calculated field, remove the date filter on it, and try updating the parameters to see if the expected output is what you were expecting!

Just one heads up, I have not tested this yet on my end, but I think it could lead to your desired outcome. Let me know if it helps!

I’ll link some AWS documentation for a few of the concepts I mentioned in case you want to better understand my process:

+Shaik Tahir : Lets try this.

Thanks A lot @DylanM for the reply, we were still struggling to enable this for our users.
Let me try the suggested approach, I will get back to you by tomorrow or day after tomorrow.

1 Like

You’re welcome @Vivek_Kundu, I am interested to see if it works out. Hopefully it at least leads us to a proper solution. Good luck!

1 Like

Hello @Vivek_Kundu, any updates on your progress implementing my suggested solution?