AWS QuickSight Launched Period function in Jan 2022, providing a powerful out-of-box date-time aware suite of comparative (e.g., year-over-year) and cumulative (e.g., year-to-date) functions. For more details about the period function use cases, refer to this blog post.
Within the suite, Period over Period (PoP) is most frequently used comparative function used to look at two or more measurable events on an annually/quarterly/monthly/weekly basis. Observing PoP performance allows monitoring of a company’s performance and seeing whether it is improving, static, or becoming worse. In Oct. 2022, we launched an enhancement to enable parameters in the POP function as place holder for period and offset arguments.
This article provides a step-by-step guide in building a time intelligence PoP (YoY, QoQ, MoM, WoW) analysis in QuickSight using Parameters. We will be using the retail-info dataset, which holds fictional transactional records for online and in store purchases. Specifically, we will be doing PoP analysis on Profits of the retail stores.
To build this solution, we are going to use function with the structure as below:
periodOverPeriodDifference(measure, date, period, offset)
-
To be able to do every type of PoP analysis (YoY, QoQ, MoM, WoW) in one visual, we will need to create a ‘Granularity’ parameter which we will be used for the ‘Period’ argument in the periodOverPeriodDifference() function. From this parameter, we will create a control that will allow the user to select the granularity from the options of: Hour, Day, Month, Quarter, Week, and Year which will define what period we want to use for the calculation.
-
We will create another parameter for the ‘offset’ argument to represent the prior time period that we want to compare against. Again, we will create a control from this parameter so now users can select things like an offset of 2, meaning the current time period will be compared against 2 time periods ago instead of the default of 1.
-
Once we have both the parameters, we are now ready to build the PoPProfit calculated field using the periodOverPeriodDifference() function. For the arguments, we will use following fields:
Measure: sum of ‘Profit’ field
Date: ‘Date’ field
Period: ‘Granularity’ parameter
Offset: ‘Offset’ parameterperiodOverPeriodDifference(sum(Profit),Date,${Granularity},${Offset})
-
Let’s also build one more calculated field to get PoPProfit in percentage using the function periodOverPeriodPercentDifference():
periodOverPeriodPercentDifference(sum(Profit),Date,${Granularity},${Offset})
-
Now let’s create a simple visual to display Date, Profit, PoPProfit and PoPProfit%. Add both the controls (Granularity and Offset) to the sheet to display them side by side. In the visual belowwe have kept the aggregation for the ‘Date’ field in the field wells at Day level. Now, if we chose Week for ‘Offset’ and 1 for ‘Granularity’, PoPProfit will return WoW for each day. For example, PoPProfit compares profit from Jan 8th 2018 to Jan 1st 2018 and returns the difference.
-
We can also change the aggregation level to week for the ‘date’ field as shown above. In this case, PoPProfit compares sum of profit for a specific week to the previous week. For example, profit from Jan 7th 2018 week is compared to profit from Dec 31st 2017.
-
Note: If the date aggregation level from the field well (i.e. week) is higher than the PoP Granularity level (i.e. day) then PoPProfit won’t return any value. Date aggregation level should be same or lower than the PoP Granularity level.
-
Now from the second last image, we can see the PoPProfit field is empty for the first week since there aren’t any prior week to compare the profits to. If we don’t want to show the empty row in our visual, we can create a dense rank for the date field and then use that field as a filter on the visual connecting it to the parameter ‘Offset’.
denseRank([{Order Date} ASC])
-
Now it will show only the records which has a higher dense rank than the offset we have selected. For example, if we set offset’s value as 5, visual will only show the records which has dense rank 6 or higher eliminating all the rows which as null value for PoPProfit.
