Calculate the number of consecutive days

Question & Answer
,
1

Hi team

I need help to how to calculate in Quicksight the number of days for each date, ensuring it meets the rule of having 6 consecutive days excluding weekends.

The goal is to calculate the count between dates that meet the criterion of being consecutive based on vacation days taken and the most recent work period. Specifically, it must satisfy a condition between two date columns (Departure) (Return), where 6 consecutive days are counted excluding weekends.

The main issue is that the data often creates a line-by-line record of the vacation days used.

image
image1182×726 82.5 KB

2

Hi @Michelle, welcome to the QuickSight 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 QuickSight

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 QuickSight.
  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 QuickSight:
  • 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 QuickSight

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

3

Hi! @Xclipse
Thank you very much for helping me with this logic. I don’t have much experience with formulas, so it’s great to have a support team. The data you provided was very helpful; in some of the formulas you gave me, the syntax did change,
such as:

Is Weekday Calculation
ifelse(extract(“WD”, {Vacation departure}) IN [1, 7], ‘Weekend’, ‘Weekday’)
I have to change to
ifelse(in(extract(“WD”, {Vacation departure}), [1, 7]), ‘Weekend’, ‘Weekday’)

This formula is giving me trouble because the syntax is incorrect. I’ve changed it several times but still haven’t found the correct syntax.
Can you help me here so I can continue with the process?

Count Consecutive Vacation Days
runningSum(ifelse({Is Weekday} = ‘Weekday’ AND not isNull({Employee ID}), 1, 0), [{Employee ID}], [{Vacation departure}], ASC)

runningSum(sum({Is Weekday} = ‘Weekday’) AND ifelse(isNotNull({Employee ID}, 1, 0)), [{Employee ID}], [{Vacation departure} ASC])

1 Like
4

Hi @Michelle, try using this function isWorkDay. Also, try using the Amazon Q Assistant on the function documentation page.

IMG_6751
IMG_67511284×1761 220 KB

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.

IMG_6753
IMG_67531650×260 18.6 KB

Hope this helps.

5

Hi @Michelle, did the Amazon Q Assistant work for you? Let us know if this is resolved. And if it is, please help the community by marking this answer as a “Solution.” (click the check box under the reply)

6

Hi
In our QuickSight, we do not have BI capabilities, but that’s okay. Thanks for your valuable help

7

Hi @Michelle – also, try this post - Period Over Period and Other Date Comparison Calculations (YoY, MoM, YTD, Rolling 90 days, etc)