How to get the max value during an over over several days

Question & Answer
, , ,
1

Hi guys,

I was wondering if anyone can help me with this as it is breaking my brain

I have the following chart of counts:

image
image1218×154 8.83 KB

The x axis is hours in a day.
this chart is filtered over a date range

what i need is the max of counts by day at each hour
for example day 1 @ hour 14 - count is 5
day 2 @ hour 14 - count is 7
day 3 @ hour 14 couunt is 3

i would want a max of 7

the problem I am having is maxover and windowmax require i have the date in the visual and as you can see I am only showing the hour.

Any thoughts on what to do here?

2

Hi @land_of_bi,
While I do not have a similar data breakdown that I could test this on, would it work if you created date parameters for your start and end timeframes and use those in your calculated field?

3

Thanks for the reply brent.

i have a parameter for start and end date which I was using to filter the main data set.

how would i use this for the calculated field? I can’t get it partitioned by date, as the date is not an attribute on the line chatrt.

1 Like
4

Hello @land_of_bi, I believe I have a way to accomplish this. You should be able to make this work by just applying the date filters directly to the visual. You can create your x-axis by using the extract function to grab the hour number:

X-Axis = extract('HH', {Date})

Now you can create the Y-Axis value by using countOver partitioned by each day at the hour level, then nest that in a maxOver partitioned by hour. This should provide the max value across all days for each hour. It would look like this:

Day and Hour = truncDate('HH', {Date})
Y-Axis = maxOver(countOver({value you want to count}, [{Day and Hour}], PRE_AGG), [{X-Axis}], PRE_AGG)

Let me know if that works!

3 Likes
5

Dude thank you so much. Worked like a charm. You’re amazing!

1 Like