Selecting a time unit in the visualization

Question & Answer
, ,
1

Hi everyone,

I’m trying to create a dynamic time unit filter for my line chart in QuickSight. I want users to be able to select “Hour” or “Minute” from a dropdown to adjust the level of detail in the time axis. Here’s what I’ve done so far:

  1. String Parameter: Created a string parameter called “TimeUnitParameter” with values “Hour” and “Minute” (single value selection).
  2. Calculated Field for Time Unit: @TimeUnit = Ifelse( ${TimeUnitParameter} = 'Hour', extract('HH', created), ${TimeUnitParameter} = 'Minute', extract('MI', created), NULL)
  3. Calculated Field for Processing Time (Not Working): @ProcessingTime = dateDiff({create_date}, {arrive_date}, [TimeUnitParameter])

I’m running into the following error with the @ProcessingTime formula:

“dateDiff({create_date}, {arrive_date}, [@Time Parameter]) for function dateDiff has incorrect argument type dateDiff(Date,Date,String list). Function syntax expects Date, Date, String.”

My questions are:

  • How can I correctly create a time unit selection that works with the line chart?
  • What’s wrong with my @ProcessingTime formula? How can I fix it to calculate the time difference based on the selected time unit (hour or minute)?

Any help would be greatly appreciated!

1 Like
2

Hello @samytar97, I definitely believe we can make this work, there are just afew things to note. First of all, we may want to create a calculated field to return the time period values that the dateDiff calculation is expecting based on the parameter value selected by the user. That would look something like this:

TimeUnit =

ifelse(
${TimeUnitParameter} = 'Hour', 'HH', 
${TimeUnitParameter} = 'Minute', 'MI',
NULL)

Now, we can use that in the dateDiff calculation to ensure it knows the time period level we want the difference value to be aggregated by. Somethine to note, I see you have TimeUnitParameter in brackets in your dateDiff calculation. I know it shows that way in the documentation, but that is to show that it is optional, you rather want to return a string value. I’ll write an example of the dateDiff calculation below, which will use the TimeUnit field I wrote above:

ProcessingTime =
dateDiff({create_date}, {arrive_date}, {TimeUnit})

This should resolve the isssues you are facing! I will mark my response as the solution, but please let me know if you have any remaining questions. Thank you!

4

@DylanM
Is there any update on the above error?

5

Can try this ?

ifelse(
${TimeUnitParameter} = "Hour", 
dateDiff({create_date}, {arrive_date}, "HH"),
${TimeUnitParameter} = "Minute", 
dateDiff({create_date}, {arrive_date}, "MI"),
NULL
)
2 Likes