Adding a calculated field based on date difference

Question & Answer
, , , ,
2

To create a calculated field in Amazon QuickSight to count devices whose estimated deadline falls within the specified date range (from tomorrow to three days from then), you can use the following approach. This calculation will help you to filter and count the devices appropriately.

  1. Create a New Calculated Field:
  • Navigate to the analysis where you have your dataset.
  • Go to the Fields List Pane.
  • Choose Add > Add calculated field.
  1. Define the Calculated Field:
  • You will need to write a calculated field formula that checks if the estimated_deadline (assuming this is the name of your field that contains the deadline dates) falls within the desired date range.
  • Use the ifelse, dateDiff, and now functions to construct this formula.
ifelse(
    dateDiff(truncDate('DD', now()), truncDate('DD', {estimated_deadline})) >= 1 AND 
    dateDiff(truncDate('DD', now()), truncDate('DD', {estimated_deadline})) <= 3, 
    1, 
    0
)
  1. Explanation of the Formula:
  • truncDate('DD', now()): This function truncates the current datetime to the date only, removing the time portion.
  • truncDate('DD', {estimated_deadline}): Similarly, this truncates the estimated_deadline to the date part.
  • dateDiff(...) >= 1 AND dateDiff(...) <= 3: This checks if the estimated_deadline is at least 1 day after today and no more than 3 days after today. It ensures the date falls between tomorrow and three days from now.
  1. Using the Calculated Field in a Visual:
  • After creating the calculated field, you can use it in a visual. For example, in a bar chart or a table.
  • Drag this calculated field into the value or filter section of your visual. Set the aggregation to Sum to get the count of devices meeting the condition.
  1. Adjust the Visual or Dashboard as Needed:
  • You might want to add more filters or additional visuals to further analyze your data.

This is just an sample approach and may contain errors without seeing your actual data and setup.

Did this answer your question? If so, please help the community out by marking this answer as “Solution!”

In case you need further assistance, 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.