Hello,
I’m trying to set up a report in which I would like to see, for the last 90 days and for the issues who have left the ‘Startedwork’ status :
For the moment I succeed to get something close as I use the ‘Average days in transition status’ measure. Can anyone bring me some help to deal with this ?
Thanks in advance
Hello @Bourgoin,
The time spent in a specific status is recorded against the date when the issue leaves the specific status. These transitions are mapped against the “Transition Status” dimension.
The easiest way to filter out activities taking place within the last 90 days is to create a calculated member within the Time dimension. However, if you want to see the data on a weekly basis - you need to create that calculated member within the Weekly hierarchy of the Time dimension.
You might use the Transition Status dimension on report pages to select the specific status.
You might create the following calculated members and measures - Calculated measures and members.
A calculated member within the Time dimension Weekly hierarchy - “Weeks of last 90 days”. The expression might be as follows. Additional options are described in the comments.
Aggregate(
[Time.Weekly].[Week].DateMembersBetween(
--90 days ago is the last day to report + 6 days to include the week ending 89 days ago
--use "90 days ago" to show weeks starting within the last 90 days
"96 days ago",
--last sunday to avoid inclusion of the current ongoing week
--use "today" if you want to show the current ongoing week
"last sunday")
)
The measure for the “number of issues in scope” in the Measures dimension might directly refer to the distinct count measure “Transitions from status issues count”.
[Measures].[Transitions from status issues count]
However, if you might have an issue leaving the status several times within the same week and consider each “cycle” separately, then you might use the transitions measure “Transitions from status”.
[Measures].[Transitions from status]
Likewise - the standard measure “Average days in transition status” considers transitions separately, and the issue spending 2 days + 4 days in the status will have the average of 3 days. You might create a calculated measure - “Average days of issue in transition status” within the Measures dimension with the following expression.
CASE WHEN [Measures].[Transitions from status] > 0 THEN
[Measures].[Days in transition status] /
[Measures].[Transitions from status issues count]
END
Since each separate “cycle” of the issue in status is not recorded as a separate entity, the Standard deviation can only be calculated on the level of issues. Since the calculation requires at least two entries, an the expression is wrapped within an additional condition.
The expression might be as follows.
CASE WHEN
--standard deviation requires more than one member for calculation
[Measures].[Transitions from status issues count]>1
THEN
StDev(
Filter(
DescendantsSet(
--set of issues
[Issue].CurrentHierarchyMember,
[Issue].[Issue]),
--filter condition - issue left status within the current Time member
[Measures].[Transitions from status]>0
),
--numeric value for Standard Deviation - days in status for the current issue in iteration
[Measures].[Days in transition status]
)
END
Regards,
Oskars / support@eazyBI.com
Hello @oskars.laganovskis,
Thanks for you reply. Now I’m very close of what I’m looking for.
With your suggestion, I have 1 issue count, 1 average and 1 std dev at the current day.
What about if I want these measures calculated on each monday since May 1st ?
The aim is to see how these measures change over the time.
Can you help me with this please ?
Regards,
Pierre
Hi @Bourgoin,
You might put the calculated member of the Time dimension on report rows and then expand it into weeks. That will show you the results for each week on Sunday evening.
Regards,
Oskars / support@eazyBI.com
I’m not sure that we are talking of the same thing.
Maybe it will be easier with an example.
What i’m looking for is to get something like this :
Date Issue count Mean Std Dev
05/06/23 10 3 1,5
12/06/23 8 2,4 1,8
19/06/23 12 2,6 1,6
Each measure should look at issues which have left the ‘Startedwork’ status during a period of 90 days before. So for the 05/06/23, it means to search during the interval [2023-03-07 : 2023-06-05]. For the 12/06/23, the period will be [2023-03-14 : 2023-06-12] and so on.
For the moment, I only get measures for one date which is the previous week :
Thanks in advance for your reply
Regards,
Pierre
Hi @Bourgoin,
Thank you for the clarification.
I initially thought you were looking to report the situation of the last 90 days for the activities happening the previous week.
Now, I see that you are looking for the activities that have taken place over the course of 90 days prior to the reporting period.
In that case, you might create a new calculated measure “Issues from status in last 90 days” with the following expression.
Cache(Aggregate(
[Time].CurrentHierarchy.Levels("Day").DateMembersBetween(
DateAddDays([Time].CurrentHierarchyMember.StartDate, -90),
[Time].CurrentHierarchyMember.StartDate
),
[Measures].[Transitions from status issues count]
))
The average days might then look as follows.
CASE WHEN
[Measures].[Issues from status in last 90 days] >0
THEN
Sum(
[Time].CurrentHierarchy.Levels("Day").DateMembersBetween(
DateAddDays([Time].CurrentHierarchyMember.StartDate, -90),
[Time].CurrentHierarchyMember.StartDate
),
[Measures].[Days in transition status])
/
[Measures].[Issues from status in last 90 days]
END
The expression for the standard deviation might then be as follows.
CASE WHEN
[Measures].[Issues from status in last 90 days]>1
THEN
StDev(
Filter(
DescendantsSet(
--set of issues
[Issue].CurrentHierarchyMember,
[Issue].[Issue]),
--filter condition - issue left status within the last 90 days
--primary filter - check if issue is relevant for context before iterating over 90 days
IIF(
--issue is relevant to context and left status anytime
([Measures].[Transitions from status],
[Time].CurrentHierarchy.DefaultMember)>0,
--perform iteration over 90 days for relevant issues only
Sum(
[Time].CurrentHierarchy.Levels("Day").DateMembersBetween(
DateAddDays([Time].CurrentHierarchyMember.StartDate, -90),
[Time].CurrentHierarchyMember.StartDate),
[Measures].[Transitions from status]),
--issue was not relevant - do not scroll through 90 days
0)
>0),
--numeric value for Standard Deviation - days in status for the current issue in iteration
--calculated over time members of previous 90 days
Sum(
[Time].CurrentHierarchy.Levels("Day").DateMembersBetween(
DateAddDays([Time].CurrentHierarchyMember.StartDate, -90),
[Time].CurrentHierarchyMember.StartDate
),
[Measures].[Days in transition status])
)
END
The expression might be slow as it needs to check if the issue left the relevant status within the last 90 days, and this has to be checked for all relevant issues.
A slight improvement is possible if the issue can only leave the status once in its lifetime.
Further performance improvements are possible if the scope of the issues can be narrowed by their absolute categories before looking into historical transitions.
Regards,
Oskars / support@eazyBI.com
Thanks for you quick response.
However it doesn’t good I think. I still have only 1 report instead of 1 per week.
Another strage thing is that this measure return ‘0’ for issues count.
Regards,
Pierre
Hi @Bourgoin ,
You might put the Time dimension members from the weekly hierarchy on the report rows instead of the calculated member. That would display the result for each displayed week.
You might click on the lower right corner of the Time dimension to also add it to report pages to narrow down the displayed set.
Regards,
Oskars / support@eazyBI.com