For a table with 2 columns, datetime and another with some strings.
How can i calculate what is the most frequent time of the day (time with the most records?)
Each datetime record has up to seconds but i want to round it up to the hour
Similarly any idea on how to do the same with days?
I am trying to calculate the records per day (month year irrelevant) and same for time for frequency per date/time.
Thanks!
Can you see if this demo can help? you can click the pencil icon on left handside and see how it build in the analysis view
https://democentral.learnquicksight.online/#Dashboard-TipsAndTricks-Calculation-Switch-Date-Aggregation
you can see it is using a calculated field “Datefield” with function truncDate
ifelse(
${Periodstarting}=‘Hour’,truncDate(“HH”,{Admit Date}),
${Periodstarting}=‘Day’,truncDate(“DD”,{Admit Date}),
${Periodstarting}=‘Month’,truncDate(“MM”,{Admit Date}),
${Periodstarting}=‘Quarter’,truncDate(“Q”,{Admit Date}),
truncDate(“YYYY”,{Admit Date})
)
1 Like
Hi @cpal Did Roy’s solution work for you? Please let us know, and help the community by marking his answer as a “Solution.”
1 Like
yeah i have done that with a count but i was wondering how can i get data on
getting all the counts per day and find the day that by average has the highest record counts for the whole data set. Or like the most popular day(s) of the week if it makes sense? Not a graph
and same for the hour truncated by the hour
I guess i can use count over the days of the week trunacted without the date part, aggregate them and then sort by DESC?