Scatter Plot "Issue Resolution Days over time"

[markusd]

Hello all,

there is this nice demo graph “Issue Resolution Days over time”:
https://eazybi.com/accounts/1000/cubes/Issues/reports/50047-issue-resolution-days-over-time

I try to do something very similar.
The only difference the Y-Axis:

I do not want the Average Resolution Days but just the Resolution Days. -> So that every Issue gets a dot (in the time row I select days -> that works).

I tried different measures instead of “AVG Resolution Days” but I could not get it to work.
Do you have some ideas?

Here a screenshot from the slightly simplified eazyBI demo graph, with the only problem left that the average groups multiple issues per day:

Big thank you in advance.

[viviane]

I came across the formula below as an example of date functions:

  DateDiffWorkdays([Issue].get('Created at'),
    [Issue].get('Resolved at'))

I created a new calculated measure with that formula. It shows up in the table view with the set up below (but only if individual issues are expanded in the table) –

• Pages: Project
• Rows: Time, Issue
• Measures: Resolution time

~ v.

[markusd]

Hello Viviane,

thank you very much for your reply.

I tried your suggestion, and I got is this far:

So it looks already pretty good.
The only thing, that there is a new color/symbol for every day … is that also in your graph?
(marked orange in the screenshot)

Thank you again, and best regards,
Markus

edit: Here is also the source for the report I created (not sure if everything is as it should be)

{
“cube_name”: “Issues”,
“cube_reports”: [ {
“name”: “Issue Resolution Days over Time - Single Issues”,
“folder_name”: “Issue Analysis Reports”,
“result_view”: “scatter_chart”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Measures”,“selected_set”:["[Measures].[Resolution date]","[Measures].[Resolution time]"],“members”:null}]},“rows”:{“dimensions”:[{“name”:“Time”,“selected_set”:["[Time.Weekly].[2018]"],“selected_set_expression”:“DescendantsSet({{selected_set}}, [Time.Weekly].[Day])”,“members”:null,“bookmarked_members”:null},{“name”:“Issue”,“selected_set”:["[Issue].[Stories and Defects]"],“members”:[{“depth”:0,“name”:“Stories and Defects”,“full_name”:"[Issue].[Stories and Defects]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false},{“depth”:1,“name”:“ATC Internal”,“full_name”:"[Issue].[ATC Internal]",“drillable”:true,“key”:“ATCI”,“expanded”:true,“drilled_into”:false,“parent_full_name”:"[Issue].[Stories and Defects]"}],“bookmarked_members”:null}],“nonempty_crossjoin”:true},“pages”:{“dimensions”:[{“name”:“Project”,“selected_set”:["[Project].[All Projects]"],“members”:[{“depth”:0,“name”:“All Projects”,“full_name”:"[Project].[All Projects]",“drillable”:true,“type”:“all”}],“bookmarked_members”:null,“current_page_members”:["[Project].[All Projects]"]},{“name”:“Time”,“duplicate”:true,“selected_set”:["[Time.Weekly].[All Times]"],“members”:[{“depth”:0,“name”:“All Times”,“full_name”:"[Time.Weekly].[All Times]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false},{“depth”:1,“name”:“2018”,“full_name”:"[Time.Weekly].[2018]",“drillable”:true,“expanded”:true,“drilled_into”:false,“parent_full_name”:"[Time.Weekly].[All Times]"}],“bookmarked_members”:null,“current_page_members”:["[Time.Weekly].[2018]"]}]},“options”:{“nonempty”:true},“view”:{“current”:“scatter_chart”,“maximized”:false,“scatter_chart”:{“show_labels”:false,“series_options”:{“Critical”:{“color”:"#4572A7"},“Major”:{“color”:"#3D96AE"},“Minor”:{“color”:"#81CEE4"},“Trivial”:{“color”:"#B5CA92"},"(no priority)":{“color”:"#E8E8E8"},“Blocker”:{“color”:"#000000"}},“y_axis_measure”:"[Measures].[Average resolution days]"},“table”:{}}}
} ],
“calculated_members”: [{“dimension”:“Measures”,“name”:“Average resolution days”,“format_string”:"#,##0.00",“formula”:“CASE WHEN [Measures].[Issues resolved] > 0 THEN\n [Measures].[Total resolution days] / [Measures].[Issues resolved]\nEND”,“annotations”:{“group”:“Default”}},{“name”:“Resolution Date”,“dimension”:“Measures”,“formula”:"[Time].CurrentHierarchyMember.StartDate",“format_string”:“mmm dd yyyy”},{“name”:“Resolution time”,“dimension”:“Measures”,“formula”:" DateDiffWorkdays([Issue].get(‘Created at’),\n [Issue].get(‘Resolved at’))",“format_string”:""}]
}

[roberts.cacus]

Hi @markusd,

I didn’t notice you had also a community post about the topic we discussed in your support ticket. Here is a recap of the solution I proposed:

To create the report you are looking for, please add the Issue dimension on rows and all the necessary dimensions in Pages. Select the “Issue” level for the Issue dimension. To display the total resolution days for every single issue, try to use the hidden measure “Total resolution days” by creating a new calculated measure with the formula below:

[Measures].[Total resolution days]

After this, select the measures “Issue resolution date”, the newly created measure with “Total resolution days” and “Issues resolved”. Now you can switch to the “Scatter” chart type. Please have a look at the pictures below on how it would look in the table and Scatter chart views:

Kind regards,
Roberts // eazyBI support

[markusd]

Yes - I first tried it via a community question.

Thank you again, the solution works perfectly