How to group daily summary data by date

Hi I have been using below query to get average response time for application for duration of a day. Is it possible to group this daily summary by date and view it in dashboard??

SELECT average(duration) FROM Transaction WHERE appName IN (… app…names…) TIMESERIES 1 hour FACET appName LIMIT 20 SINCE yesterday UNTIL today

1 Like

Hey @rohankadu

You could open up your SINCE clause to see more data over time (perhaps SINCE 1 WEEK AGO) and set TIMESERIES 1 DAY rather than 1 Hour. That’ll group the data into one day chunks for your visualisation.

Alternatively, for a list view, you can try something like this:

SELECT average(duration) FROM Transaction WHERE appName IN (... app...names...) FACET appName, dateOf(timestamp) LIMIT MAX SINCE 1 week ago UNTIL today

That’ll look like this screenshot taken of our demo data:

1 Like

Hi @RyanVeitch thanks for the solution, it helped ! :slight_smile:
I can view the data in grouped by date format for applications.
Still need to check if the requirement is satisfied with the stake holder.

Thanks for the prompt solution.

Regards,
Rohan

1 Like

Hi @RyanVeitch is it possible to have all those date on X-axis and all the application names on y-axis for visualization in table format. It will be very easy to read that way.

image

Something like this.

Hey @rohankadu I think it’s possible to do this in the other direction…

That is, App Names on the X-Axis && Dates on the Y-Axis, see below;

SELECT filter(average(duration), where appName = 'AppName1') as 'AppName1', filter(average(duration), where appName = 'AppName2') as 'AppName2' FROM Transaction SINCE 4 DAYS AGO FACET dateOf(timestamp)

You can add more apps to this with extra Filter clauses:

filter(average(duration), where appName = 'AppName') as 'AppName'
1 Like

Hi @RyanVeitch the axis are opposite but it helps.

Indeed! As I mentioned I don’t think it’s possible to get the data exactly as you wish, but with axis flipped you can get close.

1 Like