I have a report in which i’d like to look at the average time spent based on certain priority tickets. I’ve found one query that is atleast returning results, but the results seem inaccurate.
Ideally, I’d like to see the average time spent on a ‘Normal Ticket’. >drill into that average time, and see a list of all the ‘Normal Tickets’ with their ‘time spent’ listed (specific to that ticket alone).
I created two user defined formulas, but when i try to take the average of my ‘Time Spent’ formula, blanks are returned.
If i only want this table to show results for issues that were created in 2018 and onward, how would i go about that?
I brought ‘time’ in as a row, and selected 2018, but it’s pulling in some records that i would not expect to be there. (opened in 2016, finally closed in 2018). I tried to add a calculated member within ‘Time’, but it looks like i’m referencing ‘issue’ incorrectly. I then tried to add an additional calculated member within ‘Measures’ and it didnt change anything in regards to the results table.
I am pretty sure that if you select the year 2018 it works correctly. When the filter is running and it is checking the “[Measures].[Issues created] > 0” condition it also check if you have something selected in other dimensions like [Time].
Maybe you wanted issues closed in year 2018 instead? Then you can just replace the “[Measures].[Issues created] > 0” with “[Measures].[Issues closed] > 0”. Or if you want to have both then make it “[Measures].[Issues created] > 0 AND [Measures].[Issues closed] > 0”.
eazyBI service partner
With the calculation you suggested, its not calculated an avg time spent (which is good considering there was only 1 low priority ticket opened in 2018 and it is not yet closed.)… But i’m hoping it to display ‘0’ for issues resolved instead of 3.