如何在Amazon QuickSight中进行期间对比（Period-over-Period）分析？

Got it, let's figure out how to build this customizable period-over-period order comparison. The goal is to let users pick any two time periods (like Jan vs Feb 2020) and get total orders plus the percentage change between them. Here's a practical solution using Python and pandas, which is ideal for this kind of data work:

First, let's recap your sample data and desired output to make sure we're aligned:

Sample Input Data #

date       orders
2020-01-01  1
2020-01-02  2
2020-01-03  5
2020-02-01  4
2020-02-02  2

Desired Output #

Step-by-Step Implementation #

1. Load and Prep the Data #

First, we'll load the data and format the dates to make grouping by month/year easy:

import pandas as pd

# Load your dataset (replace this with your actual data source, like a CSV)
data = {
    'date': ['2020-01-01', '2020-01-02', '2020-01-03', '2020-02-01', '2020-02-02'],
    'orders': [1, 2, 5, 4, 2]
}

df = pd.DataFrame(data)
df['date'] = pd.to_datetime(df['date'])
# Add a column for month-year in "Jan 2020" format
df['year_month'] = df['date'].dt.strftime('%b %Y')

2. Calculate Monthly Order Totals #

Next, we'll group the data by month-year and sum up the orders:

monthly_totals = df.groupby('year_month')['orders'].sum().reset_index()

3. Add Custom Period Selection & Delta Calculation #

This is where we let users pick their target periods. We'll grab the totals for each period, then calculate the percentage change:

# Let users define their two periods (match the "Jan 2020" format)
prev_period = 'Jan 2020'
curr_period = 'Feb 2020'

# Extract totals for each selected period
prev_total = monthly_totals.loc[monthly_totals['year_month'] == prev_period, 'orders'].values[0]
curr_total = monthly_totals.loc[monthly_totals['year_month'] == curr_period, 'orders'].values[0]

# Calculate and format the percentage delta
delta_percent = ((curr_total - prev_total) / prev_total) * 100
delta_formatted = f"{delta_percent:.0f}%"

4. Generate the Final Comparison Table #

Finally, we'll package the results into a clean table:

# Create the result DataFrame
comparison_result = pd.DataFrame({
    f"{prev_period} Orders": [prev_total],
    f"{curr_period} Orders": [curr_total],
    "Delta Orders": [delta_formatted]
})

# Print or export the result
print(comparison_result)

Running this code will output exactly what you're looking for:

Jan 2020 Orders  Feb 2020 Orders Delta Orders
0                8                6         -25%

Customization Tips #

• Let users input periods: Wrap this logic in a function that takes prev_period and curr_period as arguments. You could even add a simple input prompt for user interaction.
• Support other time frames: If you want to compare weeks, quarters, or custom date ranges, adjust the year_month column to use a different date format (like '%Y-W%U' for weeks) or filter the original DataFrame by date ranges instead of grouping.
• Handle edge cases: Add checks to make sure the user-selected periods exist in the dataset, so you don't get errors if someone picks a month with no data.

内容的提问来源于stack exchange，提问作者moltar