How to Calculate Leads from the Same Intake in the Previous Year in QuickSight?

angelicamarrone · February 19, 2025, 9:47am

Hi everyone, I’m working on an analysis in QuickSight and need help building a KPI.

Context:

I have a dataset with the following key columns:

course_name
edition
intake (Winter, Spring, Summer, Fall)
year (2023, 2024, 2025)
n_leads (number of leads for each course/intake/year combination)
prev_intake_course_name, prev_intake_edition, prev_intake_intake, prev_intake_year, prev_intake_exist_boolean
next_intake_course_name, next_intake_edition, next_intake_intake, next_intake_year, next_intake_exist_boolean

Each course follows a different pattern: some may have intakes every year, while others may alternate. However, editions are always progressive.

Goal:

I need to calculate a KPI that sums the number of leads (n_leads) from the same intake in the previous year, based on the user’s current selection.

Requirements:

The user can apply multiple selections on the following filters:

course_name
intake
year

Examples:

1 - If I’m looking at Winter 2025, I want to sum the leads from Winter 2024, from all courses.
2 - If the user selects Course A in Winter 2025 and Course B in Spring 2024, the KPI should sum:

The leads of Course A in Winter 2024
The leads of Course B in Spring 2023

How can I structure this in QuickSight to ensure it dynamically adapts to user selections? Any insights would be greatly appreciated. Thanks!

neelay · February 19, 2025, 12:23pm

Hi @angelicamarrone

Please try using the KPI widget for your dataset focusing on lead counts. Additionally, create a controller for filtering the dataset by course name, intake, and years.

Once you have set this up, you can adjust the controller settings to specify which visuals will be affected by the changes. Make sure to select your KPI widget in this process.