Hello.
I have a question as we’re working on additional tasks.
We’d like to see trends in the average number of days from the start date to the end date.
monthly
based on “Application Date” (custom field)
Row : Time/Month
Columns : Project, Measures
Could you please give me some advice on which part I should edit to see it?
The code below produces an error message.
“Query execution timeout of 60 seconds reached.
Please try to make query simpler.”
[Measures].[TrendAver]
NonZero(
Avg(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
-- Filter by time whem the value for any issue
DateInPeriod(
-- use the Issue created date property here
[Measures].[Issue created date],
[Time].CurrentHierarchyMember
)
AND
[Measures].[Issue status] = "Done"
),
CASE WHEN
[Measures].[Issues created] > 0
THEN
DateDiffWorkdays(
-- use the Issue 접수일 property here
[Measures].[Issue Start Date],
[Measures].[Issue End Date],
'67'
)
END
)
)
DateDiffWorkdays is same what you have rather than this simplified below, as it was explained in the DateDiffWorkdays documentation
Is there any specific reason why you have a filter where you want to have the created tickets for that month and then only status = Closed?
Would it be only the status = Closed, regardless of the moment of the creation?
If not, it would be faster like this:
CASE WHEN
[Measures].[Issues resolved] > 0
THEN
NonZero(
Avg(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
-- Filter by time whem the value for any issue
DateInPeriod(
[Measures].[Issue resolution date],
[Time].CurrentHierarchyMember
)
AND
[Measures].[Issue status] = "Done"
),
DateDiffWorkdays(
[Measures].[Issue Start Date],
[Measures].[Issue End Date]
)
)
)
END
In the case you want to have only the work done, not discarded. I mean:
Work done => Resolution in (Done, Fixed, Configuration, Ready) or any other reason the team can have to mark the type of development done.
Work discarded => Resolution in (“Won’t Do”, “Won’t Fix”, Duplicated, XXX) Any other reason why the work was not done
This would be like:
CASE WHEN
[Measures].[Issues resolved] > 0
THEN
NonZero(
Avg(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
-- Filter by time whem the value for any issue
DateInPeriod(
[Measures].[Issue resolution date],
[Time].CurrentHierarchyMember
)
AND
[Measures].[Issue status] = "Done"
--If you want to exclude the rest of the Resolutions.
AND
(
[Measures].[Issue resolution] = "Fixed"
OR
[Measures].[Issue resolution] = "Done"
OR
[Measures].[Issue resolution] = "Configuration"
OR
[Measures].[Issue resolution] = "Ready"
)
),
DateDiffWorkdays(
[Measures].[Issue Start Date],
[Measures].[Issue End Date]
)
)
)
END
Thanks for your reply.
I found that filtering by resolution value is faster than issue status.
I would like to calculate only the completed tasks among those received on a specific date (application date) in that month.
For example, If a case was created in January but the application date in February, I’d like it to be counted as the February average.
Of course, this average only includes completed issues.
Because doing that, you are filtering by the time it was resolved in that specific month (it was created in Jan. and closed in Feb ==> Appears in Feb)
Then, drag and drop the rest of the desired Dimensions into the report
2nd formula is only if you want to do a 2nd filter for your issues in the case you are differentiating the work delivered or discarded (because it is not going to be the same time for a ticket developed rather than a ticket which was duplicated)
We don’t want to display cases based on the month it’s resolved.
We input the application date, and we want to filter based on that date(application date).
So I modified it as below, but some data is missing.
I want to know why it is missing and how to solve the formula.
NonZero(
Avg(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
-- Filter by time whem the value for any issue
DateInPeriod(
[Measures].[Issue application_date],
[Time].CurrentHierarchyMember
)
AND
[Measures].[Issue status] = "Done"
),
CASE WHEN
[Measures].[Issues with application_date] > 0
THEN
DateDiffWorkdays(
[Measures].[Issue Start Date],
[Measures].[Issue End Date],
'67'
)
END
)
)
When I write a CASE WHEN/THEN statement in the first line, Project Dimensions are not applied.
For example, I selected three projects and added dimensions to the columns. I expected three line graphs, but only one line graph is displayed.
The latest expression might be improved to only take the issues of the specific project if you have projects on columns.
That would speed up calculations.
The updated expression might then be as follows.
CASE WHEN
-- no selection on issues - can benefit from pre-filtering projects
[Issue].CurrentHierarchyMember is [Issue].CurrentHierarchy.DefaultMember
THEN
-- no selection on issue dimension, can pre-filter projects
NonZero(
Avg(
Filter(
Generate(
Filter(
[Issue].[Project].Members,
-- condition for relevance
[Measures].[Issues with application_date] > 0
),
-- issues from pre-filtered projects
Descendants(
[Issue].CurrentMember,
[Issue].[Issue])
),
-- Filter by time whem the value for any issue
DateInPeriod(
[Measures].[Issue application_date],
[Time].CurrentHierarchyMember)
AND
[Measures].[Issue status] = "Done"
),
-- numeric value for AVG - executed on reduced issue set
CASE WHEN
-- check against the report context
[Measures].[Issues with application_date] > 0
THEN
DateDiffWorkdays(
[Measures].[Issue Start Date],
[Measures].[Issue End Date],
'67'
)
END
)
)
ELSE
-- some selection on issues - use original expression
NonZero(
Avg(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
-- Filter by time whem the value for any issue
DateInPeriod(
[Measures].[Issue application_date],
[Time].CurrentHierarchyMember
)
AND
[Measures].[Issue status] = "Done"
),
CASE WHEN
[Measures].[Issues with application_date] > 0
THEN
DateDiffWorkdays(
[Measures].[Issue Start Date],
[Measures].[Issue End Date],
'67'
)
END
)
)
END
If there are months when some projects do not have continuous values, but the values exist in every second month, the lines could not be drawn.
If you are only seeing one line instead of three, you might switch over to the table view to see the numeric data behind the lines.
