Quicksight Hour aggregation for period over period views

[estone8819]

Hello,

I am currently trying to build a line chart that can count the total amount of x_id per hour of day and display this on a month over month or week over week view. I would like to be able to filter by hour of day for this view. For example:

In this chart, i have a calculated field called ‘Hour of Day’ and a parameter called ‘Hour Start’ that is set to 3. This is showing me the month over month view for total count “x_id” for all hours after 3 and for each month. I cannot figure out how to apply a between filter or both a greater than and less than filter to show for example, the count of x_id each month between the hours of 11 & 2. I will also provide a screenshot of my calculated field i am leveraging for my month aggregation and my hour aggregation currently, adding those below.
Hour of Day:

Date Aggregation:

Thank you in advance for any help!

[Max]

In your Hour of Day calculated field why are you saying >=3? Can you change it to =3?

[bergqdou]

Hi @estone8819,
I may be missing some complexity. However, I believe I would take a different approach. I think it would be easier to create a custom calculation for the visual:

distinct_count(ifelse({trip_reason_description}= 'Discharge', {ride_id},NULL))

and create a custom field in the dataset for the control (multiselect for your example between 11-2pm)

extract('HH', {ride_scheduled_start_time})

Your ‘date aggregation’ shouldn’t need to change. I think this will solve your problem. I will mark this as solution. If it doesn’t please uncheck ‘Solution’ for more help. Thanks!