Count issues between dates

Questions & Answers
1

Hello,

i want to get the sum of the issues in late (by several criterias)
for example i want to be able to know haw many issues are in late for more than 10 days.

i have write the formula below… but the result i am looking for does not match with the value displayed…
When I click on the cell of the table in order to get the “Drill through issue”, i find what i want! Indeed, on this secondary window, i correctly find the list of the issues wanted.

For example, when i display the “Drill Through Issue” Window, i can read on the top of the window:
Total value: 183; row count: 10
The formula used displays 183 in the table but i want the 10!

how can i udate the formula?

best regards


Sum(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    DateInPeriod(
      [Issue].CurrentMember.GetDate('Created at'),
      [Time].CurrentHierarchyMember
    )
  ),
  CASE WHEN (Int(DateDiffDays([Measures].[Issue due date], Now()))>0 and Int(DateDiffDays([Measures].[Issue due date], Now()))<10 AND [Measures].[Issue status]<>"Done")
  THEN DateDiffDays([Issue].CurrentHierarchyMember.Get('Due date'), Now())
  END
)
2

Hello,
i have found this solution, but when i click on the data in order to “drill through issue”, i have the correct list of tickets displayed but i have always “1” as value (i would like to have directly the numbers of days).

is there a way to do better?


Sum(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    DateInPeriod(
      [Issue].CurrentMember.GetDate('Created at'),
      [Time].CurrentHierarchyMember
    )
    AND
    (DateDiffDays([Measures].[Issue due date], Now())>0 AND
    DateDiffDays([Measures].[Issue due date], Now())<=10 AND
    [Measures].[Issue status]<>"Done")
  ),
  CASE WHEN [Measures].[Issues created]=1
  THEN 1
  END
)

thanks

3

Hi @clement_dd_64

Thanks for the elaborate explanation!

However, your best course of action here would be to simply use both of the measures side-by-side in the report to see both the number of issues and also the number of days.

For the first formula, you can also use the Avg() function instead of the Sum() function to return the average Due date/Now() day difference across issues.

Let me know if you have further questions on this!
​Best regards,
​Nauris / eazyBI support