Trying to get the oldest ticket in each Customer-Program

Questions & Answers
1

Hello,

I’m trying to get the oldest ticket in each “Customer - Program”. the issue I have is that I can only get the oldest in all Customer-Programs, but not individual one.

I think I need to filter the descendants for each Customer-Program, but I can’t figure out how.

Screenshot 2024-06-21 120420

Here is my code for “Max Age”

CASE WHEN
   [Customer - Program].CurrentHierarchyMember.Level.Name = "Customer - Program"
THEN
   Max(
      Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
      DateDiffDays([Measures].[Issue created date], Now())
   )
END

Thank you!

2

Hi @guillolb

Thanks for posting your question!
Can you please try the formula below and see if it returns the expected results for you?

Max(
   Filter(
     Generate(
       Filter(
         [Customer - Program].[Customer - Program].Members,
         [Measures].[Issues created] >0
       ),
         DescendantsSet(
    [Issue].CurrentHierarchyMember,
    [Issue].[Issue])
     ),
     [Measures].[Issues created]>0
   ),
    DateDiffDays([Measures].[Issue created date], Now())
    )

Best wishes,

Elita from support@eazybi.com

3

Hello,

That works correctly when I select only a small subset of data, but it hits the 60 second timeout if I include all Customer-Programs (About 45).

Is there a way to go around this?

Thank you so much!

4

Hi @guillolb

In that case, you may try the option below:

CASE WHEN
   [Customer - Program].CurrentHierarchyMember.Level.Name = "Customer - Program"
THEN
Max(
  Filter(
   DescendantsSet(
    [Issue].CurrentHierarchyMember,
    [Issue].[Issue]),
--issue relates to context
   [Measures].[Issues created]>0),
   DateDiffDays([Measures].[Issue created date], Now())
    )
END

An alternative solution is pasted below as well, in case your field “Customer - Program” is a single select field and is also imported as a property

CASE WHEN
   [Customer - Program].CurrentHierarchyMember.Level.Name = "Customer - Program"
THEN
Max(
  Filter(
   DescendantsSet(
    [Issue].CurrentHierarchyMember,
    [Issue].[Issue]),
--issue relates to "Customer-program"
   [Measures].[Issue Customer - Program] = [Customer - Program].CurrentHierarchyMember.Name),
   CASE WHEN
--issue relates to rest of the context (if any defined)
    [Measures].[Issues created]>0
   THEN
    DateDiffDays([Measures].[Issue created date], Now())
   END
    )
END

Best wishes,

Elita from support@eazybi.com

5

You are amazing!

The last one works very quickly!!!

I guess you were right : “in case your field “Customer - Program” is a single select field and is also imported as a property”

Thank you so much!

1 Like