Running Average of Created Tickets of the last 30 days | Community
Skip to main content

Running Average of Created Tickets of the last 30 days

  • July 3, 2019
  • 8 replies
  • 0 views
F

Hi

I'm trying to build a Query that will track Ticket Creation for each new day for the current month (so it will be filtered to values belonging, in this case to, July). Something like so:

The thing is I'm trying to add a trend line that showcases the rolling 30 days average for Ticket Creation as well. That's where I'm lost. There was an article written on the subject but it doesn't cover the Ticket Count when they are created:

Adding time and date calculated metrics

The was a Question asked in relation to this topic but it was never answered.

Thanks,

    This topic has been closed for replies.

    8 replies

    L
    • Lee29
    • July 3, 2019

    Glad you asked this! I've been having a doozy of a time myself trying to figure out how to get a trendline.

    In ZD Insights there was a cute "preceeding rows" but in Explore, I can't seem to figure out how to get a metric that looks at a range. 

    F
    • Frank19
    • Author
    • July 3, 2019

    Hopefully one of the experts will be able to give us a hand!

    F
    • Frank19
    • Author
    • July 3, 2019

    I'm sharing what I've done so far (that hasn't worked) in case somebody views this and corrects my mistake.

    1) Created a "Date range calculated metric" like so:

     

     

    Then I took this measure and went to the Standard Calculation and wrote this formula

     

    `SUM([Last 30 Tickets])/30`

     

    But it doesnt work. When I add this Metric, an error message says "Only partial results are visible for this query because the underlying SQL query returns more than 50,000 rows. To limit performance issues, each delegated SQL query is restricted to 50,000 rows"

    Jon46
    • Jon46
    • July 6, 2019

    Hey Frank!

    I have posted the answer to that query you mentioned that wasn't answered previously:

    Month to date this year vs. MTD LY


    I hope this tip helps get you started, please let us know if you still have questions about this kind of report!

     

     

    F
    • Frank19
    • Author
    • July 8, 2019

    Hi Jon,

    Thanks for getting back to me but I think your answer does not apply to what I'm talking about. My idea is to add a trend line that points to the last 30 day ticket creation average. So, for example, down below you are seeing two trend lines: one for the "Tickets Created - Daily Average" (yellow, and this metric is already built in) and the ticket creation of last week (in red). Also, you're seeing the ticket creation numbers of the current week as it continues to unfold in the blue bars:

     

    What I would like to add is another trend line that's going to indicate the rolling 30 day average. I tried using what you did:

    This is what is getting displayed in the bottom of the screen:

     

    F
    • Frank19
    • Author
    • July 8, 2019

    I also wanted to point out that I tried building the same query from your answer in the other thread (both replicatin step by step and by using a different timeframe of comparison) and in both cases I got the same error: 

    V

    hi @frank19,

    I'm looking how to do that too. Have you made any progress on this? I realise it was 2 years ago hah 

    Thanks 

    Cheeny
    • Cheeny
    • April 14, 2021

    Hi Vladimir,

    One of our Team member answered this query on a different post. You may check Month to date this year vs. MTD LY. I hope that helps!

     

    Terms & ConditionsCookie settingsAccessibility statement