I have a report that shows current open issues based on age interval (30-365 days). Is there a way to check a quarter before how many open issues were there based on age interval (30-365 days) ?
I want to get to this report so in the future, I can monitor quarter vs quarter , how many open issues are there each age interval end of each quarter.
–Sunray
Hi
The “Age interval” dimension, unfortunately, can not be used for that, as that dimension shows currently unresolved issues by their current age; it would not show issues’ historical age in past periods (quarter by quarter).
You may want to create a calculated measure (in Measures) that counts issues that were open at the end of the time period and then their age was between 30-365 days.
CASE WHEN [Measures].[Open issues] > 0 THEN
Sum(
Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
-- filter open issues in period using issue properties Created date and Resolution date only
DateBeforePeriodEnd(
[Issue].CurrentMember.get('Created at'),
[Time].CurrentHierarchyMember)
AND
NOT DateBeforePeriodEnd(
[Issue].CurrentMember.get('Resolved at'),
[Time].CurrentHierarchyMember)
AND
-- check if they were open 30-365 days at the end of the time period
IIF(DateInPeriod(Now(), [Time].CurrentHierarchyMember),
DateDiffDays([Issue].CurrentMember.get('Created at'),
Now()),
DateDiffDays([Issue].CurrentMember.get('Created at'),
[Time].CurrentHierarchyMember.NextStartDate)) >= 30
AND
IIF(DateInPeriod(Now(), [Time].CurrentHierarchyMember),
DateDiffDays([Issue].CurrentMember.get('Created at'),
Now()),
DateDiffDays([Issue].CurrentMember.get('Created at'),
[Time].CurrentHierarchyMember.NextStartDate)) <= 365),
([Measures].[Issues created],
[Time].CurrentHierarchy.DefaultMember)
)
END
See a similar report from the demonstration account that shows open issues age in time:
https://eazybi.com/accounts/1000/cubes/Issues/reports/87233-average-age-report
Best,
Ilze, support@eazybi.com