Restrict returned data to a dynamic Quarter

Hi @OntheRaz, here is one approach to achieve the desired dynamic calculation of quarterly spends and attainment percentages using QuickSight functions:

High-Level Approach

  1. Prepare Your Data: Ensure your dataset contains Date and Spend fields.
  2. Create Calculated Fields for Quarters and Year: Use QuickSight functions to derive these fields.
  3. Calculate Quarterly Spend: Sum the spend for each quarter dynamically.
  4. Set Hardcoded Targets: Define targets for each quarter.
  5. Calculate % Attainment: Compute the percentage attainment for each quarter.
  6. Handle Pending Quarters: Determine the current quarter and mark future quarters as “pending”.

Detailed Steps in QuickSight

1. Prepare Your Data

Ensure your dataset includes Date and Spend fields.

2. Create Calculated Fields for Quarters and Year

Year
  1. Go to the dataset in QuickSight.
  2. Click on “Add calculated field”.
  3. Name it Year.
  4. Use the formula:

Pseudocode (Syntax may vary)

truncDate("YYYY", {Date})
Quarter
  1. Add another calculated field.
  2. Name it Quarter.
  3. Use the formula:

Pseudocode (Syntax may vary)

extract("QUARTER", {Date})

3. Calculate Quarterly Spend

  1. Create a calculated field for each quarter’s spend.
Q1 Spend
  1. Add a calculated field.
  2. Name it Q1 Spend.
  3. Use the formula:

Pseudocode (Syntax may vary)

ifelse(extract("QUARTER", {Date}) = 1, sum({Spend}), 0)
Q2 Spend
  1. Add a calculated field.
  2. Name it Q2 Spend.
  3. Use the formula:

Pseudocode (Syntax may vary)

ifelse(extract("QUARTER", {Date}) = 2, sum({Spend}), 0)
Q3 Spend
  1. Add a calculated field.
  2. Name it Q3 Spend.
  3. Use the formula:

Pseudocode (Syntax may vary)

ifelse(extract("QUARTER", {Date}) = 3, sum({Spend}), 0)
Q4 Spend
  1. Add a calculated field.
  2. Name it Q4 Spend.
  3. Use the formula:

Pseudocode (Syntax may vary)

ifelse(extract("QUARTER", {Date}) = 4, sum({Spend}), 0)

4. Set Hardcoded Targets

  1. Create calculated fields for each quarter’s target.
Q1 Target
  1. Add a calculated field.
  2. Name it Q1 Target.
  3. Use the formula:

Pseudocode (Syntax may vary)

100000  // Replace with your actual Q1 target
Q2 Target
  1. Add a calculated field.
  2. Name it Q2 Target.
  3. Use the formula:

Pseudocode (Syntax may vary)

200000  // Replace with your actual Q2 target
Q3 Target
  1. Add a calculated field.
  2. Name it Q3 Target.
  3. Use the formula:

Pseudocode (Syntax may vary)

300000  // Replace with your actual Q3 target
Q4 Target
  1. Add a calculated field.
  2. Name it Q4 Target.
  3. Use the formula:

Pseudocode (Syntax may vary)

400000  // Replace with your actual Q4 target

5. Calculate % Attainment

Create calculated fields to determine the percentage attainment for each quarter.

Q1 % Attainment
  1. Add a calculated field.
  2. Name it Q1 % Attainment.
  3. Use the formula:

Pseudocode (Syntax may vary)

ifelse({Q1 Spend} = 0, 0, {Q1 Spend} / 100000 * 100)
Q2 % Attainment
  1. Add a calculated field.
  2. Name it Q2 % Attainment.
  3. Use the formula:

Pseudocode (Syntax may vary)

ifelse({Q2 Spend} = 0, 0, {Q2 Spend} / 200000 * 100)
Q3 % Attainment
  1. Add a calculated field.
  2. Name it Q3 % Attainment.
  3. Use the formula:

Pseudocode (Syntax may vary)

ifelse({Q3 Spend} = 0, 0, {Q3 Spend} / 300000 * 100)
Q4 % Attainment
  1. Add a calculated field.
  2. Name it Q4 % Attainment.
  3. Use the formula:

Pseudocode (Syntax may vary)

ifelse({Q4 Spend} = 0, 0, {Q4 Spend} / 400000 * 100)

6. Handle Pending Quarters

To show “pending” for quarters that have not been reached yet, you need to create a calculated field based on the current date.

Current Quarter
  1. Add a calculated field.
  2. Name it Current Quarter.
  3. Use the formula:

Pseudocode (Syntax may vary)

extract("QUARTER", now())
Modify Quarterly Spend Fields to Show Pending
  1. Modify the Q2 Spend calculated field to show “pending” if the current quarter is less than

Pseudocode (Syntax may vary)

ifelse({Current Quarter} < 2, "pending", {Q2 Spend})
  1. Repeat similar modifications for Q3 and Q4 spend fields:

Pseudocode (Syntax may vary)

ifelse({Current Quarter} < 3, "pending", {Q3 Spend})

Pseudocode (Syntax may vary)

ifelse({Current Quarter} < 4, "pending", {Q4 Spend})

Summary

  • Prepare Data: Ensure Date and Spend fields are present.
  • Extract Quarter and Year: Use QuickSight calculated fields.
  • Calculate Quarterly Spend: Aggregate spend data by quarter.
  • Set Hardcoded Targets: Define quarterly targets using calculated fields.
  • Calculate % Attainment: Compute percentage attainment using calculated fields.
  • Handle Pending Quarters: Use current date to dynamically set pending quarters using calculated fields.

If these steps do not resolve your issue, try using custom SQL or pre-processing at the data source.

Also, try using the Amazon Q Assistant on the functions documentation page.

Amazon Q

Build calculations with Generative BI

Another approach is using Generative BI, you can use natural language prompts to create calculated fields within QuickSight, as shown in the following image. For more information about calculated fields in analyses, see Adding calculated fields.

Amazon Q Build Calculation

Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!

In case you need further assistance with your problem, please create a sample dashboard with sample dataset showing your problem state using Arena and please create a new post, so we can look at it from a fresh perspective. (Details on using Arena can be found here - QuickSight Arena)