Calculate the number of consecutive days

Q&A
,
2

Hi @Michelle, welcome to the Quick Sight Community. Let’s dive into the process of calculating the number of vacation days, ensuring that it meets the rule of having 6 consecutive days excluding weekends.

Step-by-Step Process in Amazon Quick Sight

Step 1: Data Preparation

Before proceeding, ensure your dataset includes all necessary columns, particularly the vacation departure and return dates, along with employee IDs.

Step 2: Creating Calculated Fields

  1. Create a Date Column: Ensure you have a date column covering all possible dates. If not, you might need to generate it in your data preparation step before importing it into Quick Sight.
  2. Create Calculated Fields for Day of Week and Is Weekday:
  • Day of Week Calculation:

Pseudocode (Syntax may vary)

extract("WD", {Vacation departure})

This function extracts the day of the week from the vacation departure date, where Monday = 1 and Sunday = 7.

  • Is Weekday Calculation:

Pseudocode (Syntax may vary)

ifelse(extract("WD", {Vacation departure}) IN [1, 7], 'Weekend', 'Weekday')
  1. Running Total of Vacation Days: To track consecutive days, use the sumOver function:

Pseudocode (Syntax may vary)

sumOver(ifelse({Is Weekday} = 'Weekday', 1, 0), [{Employee ID}], PRE_AGG)
  1. Count Consecutive Vacation Days: Use the runningSum function to count consecutive weekdays:

Pseudocode (Syntax may vary)

runningSum(ifelse({Is Weekday} = 'Weekday' AND not isNull({Employee ID}), 1, 0), [{Employee ID}], [{Vacation departure}], ASC)
  1. Check for 6 Consecutive Days: Create a calculated field to check if there are 6 consecutive weekdays:

Pseudocode (Syntax may vary)

ifelse({Running Total} >= 6, 1, 0)

Step 3: Filtering and Aggregating Data

  1. Filter Out Weekends: Apply a filter to exclude weekends from your analysis in Quick Sight:
  • Use the filter pane to add a filter on Is Weekday and select Weekday.
  1. Visualization:
  • Use a pivot table or summary table to aggregate the data by employee ID and date, showing the count of consecutive days.

Example Steps in Quick Sight

  1. Create the Dataset: Ensure your dataset includes Employee ID, Vacation departure, and Vacation return fields.
  2. Create Calculated Fields:
  • Navigate to Analysis.
  • Click on Add → Calculated Field.
  • Add the calculated fields as discussed above.
  1. Apply Filters:
  • In the analysis pane, add a filter for Is Weekday.
  • Exclude weekends by selecting Weekday.
  1. Build the Visualization:
  • Use a table or pivot table visualization.
  • Add Employee ID, Vacation departure, and your calculated fields to the visualization.

Example Calculations:

  1. Day of Week Calculation:

Pseudocode (Syntax may vary)

extract("WD", {Vacation departure})
  1. Is Weekday Calculation:

Pseudocode (Syntax may vary)

ifelse(extract("WD", {Vacation departure}) IN [1, 7], 'Weekend', 'Weekday')
  1. Running Total of Vacation Days:

Pseudocode (Syntax may vary)

sumOver(ifelse({Is Weekday} = 'Weekday', 1, 0), [{Employee ID}], PRE_AGG)
  1. Count Consecutive Vacation Days:

Pseudocode (Syntax may vary)

runningSum(ifelse({Is Weekday} = 'Weekday' AND not isNull({Employee ID}), 1, 0), [{Employee ID}], [{Vacation departure}], ASC)
  1. Check for 6 Consecutive Days:

Pseudocode (Syntax may vary)

ifelse({Running Total} >= 6, 1, 0)

Visualization Example:

Create a table visualization showing:

  • Employee ID
  • Vacation departure
  • Vacation return
  • Running Total
  • Consecutive Days

This should help you visualize and ensure the vacation days meet the criteria of 6 consecutive days excluding weekends.

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 Quick Sight 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 post it here. (Details on using Arena can be found here - Quick Sight Arena