Creating period comparisons with QuickSight's periodOver and periodTo functions

[Steph]

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

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.

4. Click the newly created calculated field to add it to the table.
5. 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

3. Resize visuals to fit below the Sales comparison table
4. 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.

5. 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

3. Change the visual type to KPI
4. Move and resize visual to fit below the Sales comparison table
5. Click the pencil icon to open the Format visual pane.
6. In the KPI section, untick “Show progress bar”
7. Change the comparison method to Difference as Percent (%)
8. Change the title to “Current period vs same period last year”
9. 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

[acharchar]

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

[Steph]

Hi @acharchar, I’m glad you found this helpful!
I think it depends what you’re trying to do with the runningSum. I took the periodOverPeriodLastValue out of your calc and changed it to

runningSum(sum(Sales),[{Date (Period)} ASC],[truncDate("MM",{Date (Period)})])

This is basically telling it that for each Month I want go get a running total, and then reset at the start of the following month (the partition).

In my dataset, with my granularity set to Day this gave me the following. You can see in the table the values for December start afresh and add each day to the RunningSum. Likewise the graph is quite clear.

Is that what you’re looking to do?

[acharchar]

Hi, @Steph. Thanks for the followup. The purpose of the periodOverPeriodLastValue calculation was to create a symmetrical runningSum comparison month-over-month-over-month, said another way comparing the same number of days in each period. In your example, taking it one step further by having another runningSum column for the prior month and month offset. Therefore, on a visual, you would have three lines, showing the runningSum up to a specific day (the 8th for each respective month for example), giving you a like-to-like-to-like comparison (essentially overlaying each period).

The formula I used achieved this (screenshot below) but occasionally (rare) threw misleading numbers at the end of a period (skipped a day). I ultimately wanted to confirm my approach. We’ve made great strides with QuickSight and appreciate the support.

Numbers anonymized

[AadiG]

@Steph Thanks for sharing the details. It is really helpful. However, I am wondering if the same logic can be used for comparing a field (string) across two different dates? For example, in IT landscape, on 01 Jan 2023, RDS instance type for instance ID A was “t3.medium” and on 07 Jan 2023 the RDS instance type for same instance ID was “t2.small”.

[Steph]

Hi @AadiG Since the comparison is between measures, these would need to be numeric. I created an example for you which you could use though as follows:

My dataset looks like this

Date,InstanceName
2023-01-01,t3.small
2023-01-02,t3.medium
2023-01-03,t3.medium
2023-01-04,t3.small
2023-01-05,t3.small
2023-01-06,t2.small
2023-01-07,t2.small
2023-01-08,t2.medium
2023-01-09,t2.medium
2023-01-10,t3.large

I created three calculated fields in the data prep area
InstanceID:

ifelse(
startsWith({InstanceName},'t4g'),100,
startsWith({InstanceName}, 't3'),200,
startsWith({InstanceName},'t2'),300,0)

InstanceModel:
ifelse(

contains({InstanceName},'micro'),10,
contains({InstanceName}, 'small'),20,
contains({InstanceName}, 'medium'),30,
contains({InstanceName}, 'large'),40,0)

InstanceValue:
{InstanceID} + {InstanceModel}

The Instance value is what you would use as a measure.

You could do this in a single field, I just wanted to make it simple to read, and you might want to change the values I assigned to each.

Then in the analysis you can create your comparison.
When you use the periodOver functions, it will use all columns in your table, so if you want to include the InstanceName in your visual, you could use the lag function instead.

Change:
sum(InstanceValue)-(lag(sum(InstanceValue), [Date ASC], 1))

I also applied some conditional formatting, and get this output.

Hope that helps!

[daylightfinisher]

Dear @Steph, I am also trying to get better visibility of an orderbook and your example is super helpful. I am - on top of the YoY comparison trying to solve another problem:
My datastructure looks like this: Quantity ordered, Order date, Delivery date, Order status
To give a realistic view on the orderbook, and also to be able to compare, I would like to display in one bar chart on one x-axis: the positive quantities on order date, and the corresponding negative values on delivery date. However, I can’t create an “independent” x-axis, that sums quantities by order date, and quantities by delivery date. Would I have to manipulate the dataset, or is it possible to solve that in an analysis?

[Mykhailo_Sorochev1]

Hi @Steph , it’s a good sample, very useful, thank you! Here is a question: can I use variables (field) as an offset parameter in periodOverPeriod functions? I see I can use Parameter there, but when I try to use field - I receive error message: periodOverPeriodPercentDifference(ACoSbyTime,{time_window_start},DAY,${Interval}) - it works
periodOverPeriodPercentDifference(ACoSbyTime,{time_window_start},DAY,{Interval1}) - it doesn’t
Thank you!

[Kristin]

@Jesse Any thoughts on this?

[joeyu629]

Hey @Steph, this is super helpful. Thank you for putting this together.

I would like to add ‘Week’ as a time period control and I can successfully add it to the controls and I see that the Date (Period) field in the table visual updates to show weeks instead of days/months/quarters but my Previous Year’s data seems to have trouble populating (ie. it’s blank). Any thoughts on how to add ‘Week’ as a functioning control option?

[Jesse]

Hi @daylightfinisher - I thin kin this case you need to get both date fields into the same column in order to plot them on the same X-axis. Best way to do this is to UNION the table with itself, and in the process align the date fields into the same field, and introduce a new field that describes which date type that row belongs to (can be used as a filter later for other use case). Would be conceptually like this:

Select
field 1 as field 1,
field 2 as field 2,
order date as date,
"order date" as datetype
from table
UNION ALL
Select
field 1 as field 1,
field 2 as field 2,
delivery date as date,
"delivery date" as datetype
from table

Then you put that single date field on your x-axis. For the two values, I didnt quite follow the ‘positive/negative’ part of it - you may need some calculated fields like:
ifelse(datetype='delivery date', value*-1, value)

Which would make any rows for deliveries a negative value.

[Jesse]

Hi @joeyu629 - see step 10 in the original article. This shows how to turn a date filter into a date ‘hider’ that will keep the previous year data in the query, but will hide it from your visual. Also a similar version of this solution showcased here:
https://democentral.learnquicksight.online/#Dashboard-TipsAndTricks-Calculation-Year-over-Year-Table-with-Filtering-aka-Date-Hider-

[Jesse]

Hi @Mykhailo_Sorochev1 - not 100% sure on this one. You are saying using a parameter as the offset value works, but using a real field does not? If so, it could be that since this calculation is working across many rows of data, the value of that field might be different in those rows, and QS doesnt know which value to use. Whereas a parameter has a single value at all times.

[Mat]

Hi @joeyu629, did you manage to add ‘Week’ into time period control? I have the same problem and didn’t find any solution.

[akashmanjunatha123]

@Jesse I have one question here, how can I hide the tool tip values for filtered one? for example I have offset, previous and present year legend, I want to hide offset(offset set to zero) but the offset will still show as previous year in tool tip, how to hide that?

[Jesse]

Hi @akashmanjunatha123 - Im not sure I 100% understand the requirement, but have you seen how to remove fields from a tooltip? Tooltips on visual types in QuickSight - Amazon QuickSight

[Anubhav_Gupta]

Hi @Steph Thanks for the great article. I need your help in comparing the weekly data with the date range and granularity of one minute on x axis. Currently when I compare two week data, it is showing only one point on x axis, i.e for week start date but I need to show the transactions with the granularity of one minute for that week and previous week.
Any help will be appreciated. TIA

[Jesse]

@Anubhav_Gupta Did you change the granularity of your datetime field on X-Axis to minute?

[Anubhav_Gupta]

@Steph

addDateTime

Yes @Jesse , as you can see in the screenshoot, granularity is set to minutes and even days(for ex - 24 July) is missing on x-axis.

[Jesse]

For Previous Week, make sure your calc looks like this:
periodOverPeriodLastValue(sum({Value field}), {Datetime field}, WEEK, 1)

Then for your ‘filter’ (really a ‘hider’) add a calc like this and then add a filter control for it:
maxOver(max({Datetime field}), [{Datetime field}])

Then if you set the X-axis at minute granularity it should show you both lines. Line charts support up to 10k points, so if you actually have data for every single minute you wont be able to display it all at once (1 week of minute level data is 60247=10080 points). If you display at hourly level you will be fine, or you need to limit to less than 1 week.

I just did a ‘Previous Day’ version (rather than Previous Week) and I am able to see both lines overlaid.