Creating period comparisons with QuickSight's periodOver and periodTo functions

This article uses the SaaS-Sales Dataset that you can download as part of the QuickSight Author Workshop. We will update the data to the current date so your calculations are realistic.

If you do not have a QuickSight account please follow the instructions in the Initial Setup. Instructions to create the dataset can be found in Exercise 1 of the Build your first dashboard. Create an analysis for the dataset.

The following steps show you how to create visuals to show data from the current year, previous year, and any selected year based on user selection. Data can be displayed as day, month, quarter, or year depending on a selection made by the dashboard user.

Note - it is important to remember that you will not always be comparing complete periods. The periodOverPeriodLastValue and periodOverPeriodPercentDifference functions will get the data for the entire period in question. For period to date functions, you can use periodToDateSum. This article shows both methods for creating period comparisons.

This is what we’re going to create:

Update the dataset

Create a calculated field for Adjusted Order Date

Note - we are doing this step in order to dynamically shift all the dates in our dataset up to the current date. This will make it easier to validate the calculations and ensure all other calculations in this post always return data in the future.

  1. In the analysis, Click “Add” – “Add calculated field”.
  2. Name it “Adjusted Order Date”
  3. Enter the following formula and click Save
addDateTime(dateDiff(maxOver({Order Date}, [], PRE_AGG), now()), 'DD', {Order Date})

This will shift the date values up to the current date.

PeriodOver comparisons

1. Create a parameter and control for the reporting period

  1. Click Parameters, Create one…
    Name: PeriodStarting
    Data type: String
    Values: Single value
    Static default value: Month

  2. Click Create

  3. On the parameter added window, click Control
    Name: Period
    Style: List
    Values: Specific values
    Define specific values:
           Day
           Month
           Quarter
           Year

  4. Tick “Hide Select all option from the control values if the parameter has a default configured”

  5. Click Add

  6. Expand Controls, click on the ellipses for the Period control and click Move to sheet

  7. Move to the top left corner and resize

Image showing PeriodStarting parameter and date Period control|

2. Create a calculated field for date aggregation

  1. Click “Add” – “Add calculated field”.
  2. Name it “Date (Period)”
  3. Enter the following formula and click Save.
ifelse(
${PeriodStarting}='Day',truncDate("DD",{Adjusted Order Date}),
${PeriodStarting}='Month',truncDate("MM",{Adjusted Order Date}),
${PeriodStarting}='Quarter',truncDate("Q",{Adjusted Order Date}),
truncDate("YYYY",{Adjusted Order Date}))

This sets the value to the truncated portion of the data.

3. Add a table

  1. If you still have the AutoGraph visible on the analysis, click on it, otherwise click “Add” – “Add visual”
  2. Change the visual type to Table
  3. Click on Date (Period) and Sales to add them to the table
  4. Note how changing the Period control changes the aggregation in the table.
  5. Update the title to “Sales comparison”

4. Calculate previous year sales and add to table

  1. Click “Add” – “Add calculated field”.
  2. Name it “Previous Year Sales”
  3. Enter the following formula and click Save.
    periodOverPeriodLastValue(sum({Sales}), {Date (Period)}, YEAR, 1)

The periodOverPeriodLastValue function allows you to calculate the last previous value of a measure (in this case Sales), using a time period (Date (Period), for a specific granularity and offset. In this case, Sales from 1 year ago.

  1. Click the newly created calculated field to add it to the table.
  2. Test by setting Period to Month, and sort by Date (Period) latest first (z->a). Verify that the Previous Year Sales for the most recent period is equal to Sales for the same period of the previous year (e.g. October 2022 Previous Year Sales = October 2021 Sales)

5. Create a parameter and control for Years Offset.

  1. Click Add, Add parameter
    Name: YearsOffset
    Data type: Number
    Values: Single value
    Static default value: 1

  2. Click Create

  3. On the parameter added window, click Control
    Name: Years Offset
    Style: Slider
    Minimum: 1
    Maximum: 2
    Step size: 1

  4. Click Add

  5. Expand Controls, click on the ellipses for the Years Offset control and click Move to sheet

  6. Resize and align next to the date range control

6. Calculate offset year sales and add to table

  1. Click “Add” – “Add calculated field”.
  2. Name it “Offset Year Sales”
  3. Enter the following formula and click Save.
    periodOverPeriodLastValue(sum({Sales}), {Date (Period)}, YEAR, ${YearsOffset})
  4. Click the newly created calculated field to add it to the table.
  5. Set Period to Year. Change the Years Offset to 2. Verify that the Offset Year Sales for the most recent year is equal to Sales for 2 years ago.

7. Calculate percentage difference for previous and offset year

  1. Click “Add” – “Add calculated field”.
  2. Name it “vs Previous Year”
  3. Enter the following formula and click Save.
    periodOverPeriodPercentDifference(sum({Sales}), {Date (Period)}, YEAR, 1)
  4. Click the newly created calculated field to add it to the table.
  5. Change the format to Percent either in the Field Wells by clicking the arrow next to the field, or clicking the ellipses for the field in the Fields list and selecting Show as: percent
  6. Click “Add” – “Add calculated field”.
  7. Name it “vs Offset Year”
  8. Enter the following formula and click Save.
    periodOverPeriodPercentDifference(sum({Sales}), {Date (Period)}, YEAR, ${YearsOffset})
  9. Click the newly created calculated field to add it to the table.
  10. Change the format to Percent

8. Display current, previous and offset sales as a line chart

  1. Click “Add” - “Add visual”
  2. Select the following fields:
  • Date (Period)
  • Sales
  • Previous Year Sales
  • Offset Year Sales
  1. Resize visuals to fit below the Sales comparison table
  2. Click the pencil icon to open the Format visual pane.
  • Expand title and change the title to “Min/Max sales by period”
  • Expand x axis and untick show zoom
  • Expand data labels and click “Show data labels”. In the Label pattern dropdown, select Min/max values.
  1. Change the period and notice how the changes reflect in both the table and line chart.

Note - the date on the X-axis reflects the date for the Sales measure. The lines show the previous and offset values relative to that date. If you select Month and hover over Apr1, 2020 you will see data for Sales, Previous Year Sales (which will be the 2019 value), and the Offset Year Sales value.

Filter the data

9. Create a control for a Date Range

  1. Select the table visual, and then click Filter on the left of the screen. Click “Add Filter”
  2. Choose Date (Period) and click the ellipses to Edit.
  3. Set Applied to as “Some visuals" and select "Min/Max sales by period” as well as “Sales comparison”
  4. For the Start date set Rolling: Start of this year
  5. For the End date set: Rolling: Today.
    These are default values, which the dashboard user can change to view different data.
  6. Click Apply
  7. Click the ellipses and click “Add to sheet”
  8. Align below Period and Years Offset controls

Note - we have now lost the previous year and offset year data, because we are applying a filter before we’re running the aggregations. We need to change this to allow our previous year data to be visible. We will do this my manipulating where the filter happens in the query pipeline to effectively ‘hide’ the data in the visual rather than ‘filter’ it out of of the underlying query.

10. Create a calculated field for filtering by date range

  1. Click “Add” – “Add calculated field”.
  2. Name it “Date Hider”
  3. Enter the following formula and click Save.
    minOver(min({Date (Period)}), [{Date (Period)}], POST_AGG_FILTER)
  4. The POST_AGG_FILTER argument allows you to apply filters after the aggregation has been done. See also https://democentral.learnquicksight.online/#Dashboard-TipsAndTricks-Calculation-Year-over-Year-Table-with-Filtering-aka-Date-Hider-

11. Replace the Date Range control

  1. Delete the Date Range control and the existing filter, and create a new filter based on the new Date Hider calculated field. Apply to “Some visuals” and select “Min/Max sales by period” as well as “Sales comparison”
  2. Use the range Rolling: Start of this year to Rolling: Today
  3. Apply, add the control to the sheet, and align below Period and Years Offset controls.
  4. Click the pencil icon to edit, and change the title to “Date Range”
  5. Expand Control options, and change the date format to YYYY/MM/DD
  6. Change the date range and notice how the changes reflect in both the table and line chart.

Period To Date

Notice that the current period is showing a larger difference in previous and offset years than other periods. Depending on the time of year you may see smaller or larger differences, and it may be more obvious in one granularity setting than others. In the screenshot above using Quarter, you can see that the difference between current and previous is over 70%. This is because periodOverPeriod functions assume an entire period, and the current period is incomplete.

12. Add a calculated field for current period to date

  1. Click “Add” – “Add calculated field”.
  2. Name it “Current Yr PTD”
  3. Enter the following formula and click Save.
ifelse(${PeriodStarting}='Day',periodToDateSum(Sales, {Adjusted Order Date}, 'DAY', now()),
${PeriodStarting}='Month',periodToDateSum(Sales, {Adjusted Order Date}, 'MONTH', now()),
${PeriodStarting}='Quarter',periodToDateSum(Sales, {Adjusted Order Date}, 'QUARTER', now()),
periodToDateSum(Sales, {Adjusted Order Date}, 'YEAR', now()))

13. Add a calculated field for previous year period to date

  1. Click “Add” – “Add calculated field”.
  2. Name it “Previous Year PTD”
  3. Enter the following formula and click Save.
ifelse(${PeriodStarting}='Day',periodToDateSum(Sales, {Adjusted Order Date}, 'DAY', addDateTime(-1, 'YYYY', now())),
${PeriodStarting}='Month',periodToDateSum(Sales, {Adjusted Order Date}, 'MONTH', addDateTime(-1, 'YYYY', now())),
${PeriodStarting}='Quarter',periodToDateSum(Sales, {Adjusted Order Date}, 'QUARTER', addDateTime(-1, 'YYYY', now())),
periodToDateSum(Sales, {Adjusted Order Date}, 'YEAR', addDateTime(-1, 'YYYY', now())))

14. Add a KPI to show period to date comparison

  1. Click “Add” - “Add visual”
  2. Select the following fields:
  • Current Year PTD
  • Previous Year PTD
  1. Change the visual type to KPI
  2. Move and resize visual to fit below the Sales comparison table
  3. Click the pencil icon to open the Format visual pane.
  4. In the KPI section, untick “Show progress bar”
  5. Change the comparison method to Difference as Percent (%)
  6. Change the title to “Current period vs same period last year”
  7. Change the period value and note that the KPI values are affected by the selected granularity.

Note - if you see an error “Table calculation attribute reference(s) are missing in field wells”, check that the Date Hider filter is not being applied to this visual.

Also, check out the following related resources related to calculations.
Level aware calculations
Order of evaluation
QuickSight Workshop - Calculations
Ask the expert - Level aware calculations

6 Likes

HI! This post was incredibly helpful and set the foundation for my visual. One change I made was making the graph a running sum by using the following formula:

runningSum(
periodOverPeriodLastValue(sum(Total), {Billing Record Create Date_USE},MONTH, 1),
[{Billing Record Create Date_USE} ASC],
[truncDate(“MM”,{Billing Record Create Date_USE})])

Today though I unearthed that the above formula (as of 30th in Nov) accounted for the …27, 28, 29 in October but skipped the 30th and summed the 31th. My general question though is this the right approach to visualizing this as a running sum?

Thanks