Trying to get Average age (from custom fields) by priority

[guillolb]

Hello,

I hope you can help me.

I’m trying to get something similar to this:
https://eazybi.com/accounts/1000/cubes/Issues/reports/52577-average-age-of-unresolved-issues-by-issue-type-and-priority

But with a few changes:

However, I’m using a custom field for the creation date:

• “Issue PL Report Date”

This field is not always filled in, so I will use “Issue created date” if the other field is empty…

So I created the following Calculated Measure to get the age of every issue:

Age - from Report date

CASE WHEN IsEmpty([Measures].[Issue PL Report Date])
     THEN DateDiffDays([Measures].[Issue created date],now())
ELSE DateDiffDays([Measures].[Issue PL Report Date],now())
END

then, all I would need to do is calculate the average of the above, which seems to be something like this, but I cannot get it right:

Avg(
  Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
    [Measures].[Issues created]-[Measures].[Issues resolved]>0
  ),
[Measures].[Age - from Report date]
)

By the way, I have a custom field for the Issue Severity, but I think it’s not important here.

Please let me know how to make it work.

Thank you!

[guillolb]

It works now and it doesn’t time out.
I think the key was to use this:

Avg(
  Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
    [Measures].[Issues due]>0
  ),
[Measures].[Age - from Report date]
)

[Marita_Norvele]

Hi @guillolb,

Your solution is correct! Alternatively, you can also use [Measures].[Issues created] > 0 filter in your calculation:

Avg(
  Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
  [Measures].[Issues created] > 0
  ),
  [Measures].[Age - from Report date]
)

Best,
Marita // support@eazybi.com

[guillolb]

I could try that too and then just filter to open tickets in “Pages”, as I only want open tickets.