Insights recipe: Time-over-time reporting Follow

Comments

23 comments

  • Avatar
    Thirumal Maram (Edited )

    Whats the select statement that went into "This Month" ?

    Thanks

  • Avatar
    Solène Regnier

    Hello!

    Are you referring to the first custom metric called Last month?
    If that is the case you will find it under Elements>Date(Ticket Created)>Month/Year(Ticket Created).
    Below is a link explaining time dimensions in Insights:

    https://support.gooddata.com/entries/23204997-Zendesk-Date-Dimensions-

    I hope that helps!

  • Avatar
    Jacob J Christensen (Edited )

    @

    Great recipy!

    @

    Looks like the #Tickets column label was just renamed to This month

     

    I could not locate the exact attribute used here (Month/Year (Created)), I assume it was renamed to the one I could find (Month/Year (Ticket Created) ).

  • Avatar
    Fina Silva-Santisteban (Edited )

    Hi Holly, this is a great recipe! Thank you so much!!

    I just wanted to mention something I (and maybe also others) was having issues with:

    Entering 'PREVIOUS (some date metric)' gives out an error message, but it's just a syntax issue. The way Insights/GoodData wants it now is 'some date metric = PREVIOUS'. It's also used that way in predefined metrics:Hope this helps!

    Best,

    Fina

  • Avatar
    Jessie Schutz

    Thanks for hopping in, Fina!

  • Avatar
    Mo (Edited )

    @

    Hey Guys,

    I was wondering if someone can lend some help which would be greatly appreciated.

    I setup the table successfully regarding the months tickets were created but I'm stuck on adding the percentage difference between the months. I tried creating the metric in the "advanced custom metric" section with no luck. I've spent days but still for the life of me can't get the same table as Holly shows it in her example above.

    Any insight here and step by step instructions would be really helpful. Maybe I can get the actual table so I can go into the metrics and view how it was created and follow from there.

    Please get back to me as soon as you can.

    Thanks in advance :)

    MK

  • Avatar
    Jacob J Christensen

    Hi Mo,

    I got the same % Diff metric statement as in Holly's recipy.

    SELECT #Tickets / Last month - 1

    Maybe if you could show your `Last month` setup it would be possible to figure out.

    What happens if you use the above metric statement?

  • Avatar
    Mo (Edited )

     

    @

    Thanks for the reply Jacob,

    I tried adding the "Last Month" but this metric needed to be created in the Advanced Customer Metric which I still tried to create but failed. 

    Do you know the proper string to create "Last Month in Advanced?

    I just need direction or steps on how to create the "Difference" column or "Last Month" in advanced customer metric please. 

    Mo

  • Avatar
    Mo

    @

    @

    @

    @

    @

     

    Anyone out there? :)

    Please see my comments above. Any help would be amazing right now :(

    Mo

  • Avatar
    Jacob J Christensen (Edited )

    OK, sounds like the challenge is using the Advanced Metric Editor - we've all been there.

    What I find the easiest is, when possible, copy and paste the metric statement into the text field.

    In the case of the Last Month metric:

    SELECT # Tickets FOR PREVIOUS ( Month/Year (Ticket Created))

    Now you need to connect the parts of the statement that are more than text # Tickets (Metric) and Month/Year (Ticket Created) (Attribute).

    You do that by selecting them one at a time and finding the correct element from the right hand menu and insert it.

    Like so for # Tickets:

    You'll find # Tickets here.
    Metrics > # Tickets

    Month/Year (Ticket Created) is here:
    Attributes > Date (Ticket Created) > Month/Year (Ticket Created).

    It can be tricky sometimes to find the correct elements, it takes some trial and error or experience.

    Hope this helps.

    BTW: You don't need to call out people using @username, it doesn't do anything, everyone who follow this article will get notified.
    I don't think Holly has been active in this HC for some time, so I wouldn't expect an answer from her.

  • Avatar
    Russ Hatfield

    Hi there,

    I'm looking for a time-over-time report similar to the month-over-month one here, which I did get to work.

    However, I need it to be a different time period: Date (Ticket Created) = Last 30 days compared to the 30-day period before that. So, NOT comparing a built-in period like Month, Quarter, Year but a "rolling" one.

    Example, if I run report on 4/15/2017 it would compare period 3/15/2017 - 4/15/2017(ie last 30 days) to 2/14/2017 - 3/14/2017(ie the 30 days period before the most recent 30-day period). I'm using approx dates in this example so whatever the exact 30-day periods would be is what I'd need ; )

    Presumably, I could do similar for other "rolling" periods, such as "Last 2 weeks" compared to the 2 week period before that.

    The month-over-month is helpful but if I run report earlier in a given month the my Current Month versus Previous Month metrics are not comparable directly(unless I do manual estimation) since I might be comparing only, for example, 5 days of current month data to an entire month's data(eg if I run the report on March 5th, for example).

    Thanks!
    Russ
    russh@zillow.com

  • Avatar
    Jacob J Christensen

    Hi Russ,

    There's a Created Last 30 Days metric that I think you can use instead of the Last Month custom metric in this report.

    I haven't tried it out myself, but if I understand you correctly this should be what you're looking for.

  • Avatar
    Jessie Schutz

    Thanks for jumping in, Jacob!

    @Russ, I see that you also posted this question in the comments of another article. I'm checking to see if we have anybody who can jump in with more details there.

  • Avatar
    Justin Smith

    Has anyone here created a report similar to this:

    # tickets created AND # tickets solved

    Today, Last 7 days, Last 30 days COMPARED to the previous period? (i.e. today compared to yesterday, last 30 days compared to the 30 days before, etc.)

    I'm curious if it's possible to show all this in one table (created and solved, for three time filters, and comparing time over time reporting)

  • Avatar
    Josh Rosenthal

    Hey Justin!

    It should be possible to show all three of those periods in a single table by building out the metrics for each time period and ticket event and applying them all to the same report.

    However it may be a little cleaner on the report side to create separate reports and include them on a dashboard to view them together.

  • Avatar
    Justin Smith

    Hey Josh. Thanks - I think I am going to create three separate tables: today, last 7, last 30 and use custom metrics in each table. Otherwise it gets very choppy.

  • Avatar
    Justin Smith

    How would I create a custom metric that shows % change in tickets created today vs. yesterday? 

  • Avatar
    Natalie Miles

    Thanks for putting this guide together!

     

    I'm trying to do the same thing but using # Tickets Solved over the Last 30 days, and I can't seem to get this metric to calculate correctly (this metric is saying there was a 4,087% increase in number of solved tickets over the last 30 days when in reality it was only 4.84%).  Is there potentially something wrong with my metric?

     

     

     

  • Avatar
    Jacob J Christensen

    Hi Natalie,

    If I understand you correctly this is just a formatting issue?

    In the US and elsewhere you use the decimal point (.), whereas in other parts of the world we use the decimal comma (,).

    If I'm not totally misunderstanding you, what you should wan't to do is edit the Metric Format, so that the parts that say #,##0% instead become #.##0% (or #.#0% if you wan't to remove that third decimal also).

    Hope that helps.

  • Avatar
    Natalie Miles

    Hi Jacob--thanks for your prompt reply!

    It looks like what I'm encountering is a calculation issue and not a formatting issue.

    For instance if I manually pull the number of tickets that were solved in the last 30 days (Date (ticket solved)) is from 31 days ago to 1 day ago) I get 96,705 tickets solved, and if I pull number of tickets solved (from 61 days ago to 31 days ago) I get 94,762 tickets solved which should be a percent difference of +2%.  When I use the percent difference custom metric above I get a percent difference of +4053.20% even after adjusting the formatting issue you outlined above.

    I can't figure out why the calculation is so wildly off...

  • Avatar
    Justin Smith

    Hey Natalie - 

    Not sure if this is helpful, and I'm not sure how the "Solved Last 30 Days" metric is set up, but I used this custom metric:

    Name: % Δ in Solved Tickets

    Formula: 

    SELECT (SELECT # Tickets WHERE Date (Ticket Solved) BETWEEN THIS-29 AND THIS) / (SELECT # Tickets WHERE Date (Ticket Solved) BETWEEN THIS-59 AND THIS-30)-1

    Maybe try that and let us know how it works for your stats?

  • Avatar
    Natalie Miles

    Yassss Justin--that's it!  Works like a charm.  Thanks for your help!

  • Avatar
    Jessie Schutz

    Justin, you're a rock star! Thanks for helping out. :)

Please sign in to leave a comment.

Powered by Zendesk