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

Question & Answer
, , , , ,
1

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!

2

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.

3

I probably didn’t explain myself clearly, sorry!

I would like to have both the actual value and the comparison with the past value in the same KPI.

This first table is an example of the dataset.

immagine
immagine1243×453 67.1 KB

This second table applies Example 1, where I select the Winter intake and year 2025 from the filters.

immagine
immagine1244×465 80.6 KB

immagine
immagine245×280 5.25 KB

This third table, instead, applies the selection based on courses.

immagine
immagine1244×486 83.7 KB

immagine
immagine219×299 6.21 KB

The actual is not a problem, I’m already doing what you suggested.
How can I do the other part?

4

@angelicamarrone I don’t know if it is possible to put 2 aggregated values in 1 kpi. Sorry i couldn’t be more helpful

5

Hi @angelicamarrone,
To make a comparison in a KPI, you would need to create a calculated field for your past value (I’m not sure what your formula should be based on your levels of aggregation) and then use that as your ‘trend group’ option in the KPI. That would be the best work around for your case.

Let us know if you have any additional questions regarding this post.

6

Hi @angelicamarrone,
It’s been awhile since we last heard from you, did you have any additional questions regarding your topic or were you able to find a work around?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

7

Hi @angelicamarrone,
Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community.

Thank you!