New calculated member for tickets opened for more than three months

Questions & Answers
, ,
1

Hello, I would like to create a new calculated member for all tickets that has been opened for more then three months and still not solved. I am not able to create this one so I am asking for your help here.

Thank you in advance,
Martin.

2

Hi @martovsky ,
Welcome to eazyBI Community! :wave:

You can create an aggregate member in the Time dimension using this formula:

Aggregate(
  [Time].[Day].DateMembersBetween('3 months ago', 'today')
)

And then, create a new calculated measure in the Measure dimension to subtract from all unresolved issues those issues created in the last three months. In this way, you will get a number of unresolved issues that have been created more than three months ago and still not been solved:

NonZero(
  ([Measures].[Issues created],
  [Resolution].[(unresolved)],
  [Time].CurrentHierarchy.DefaultMember)
  -
  ([Measures].[Issues created],
  [Resolution].[(unresolved)],
  [Time].[Last 3 months])
)

Best,
Gerda // support@eazyBI.com

3

Hello,
thank you for your answer! :slight_smile:
Anyways this formula shows me tickets created in less than three months :/.

I think maybe the “minus” operator does not work right, I have not any other ideas what is wrong.

Any other idea?

Thank you so much
Martin

4

Hi @martovsky ,
Please check that created aggregate member in the Time dimension is called “Last 3 months” as that is the member that is being referenced in the next formula.

best,
Gerda

5

Thank you so much, exactly this was the problem, now it’s working great.

I tried to extend this formula, but I wasn’t successful. Is it possible to help me a bit more please?
I need to constrain the time from both sides. I mean I would like to report the issues which are open for more than three months but less than let’s say six months.

Thank you again in advance
Martin

6

Hi @martovsky ,
In that case, you can adjust the first part of the formula to calculate issues created (and still unresolved) in the last six months. Create an aggregate member “Last 6 months” in the Time dimension (similar to the last 3 months) and adjust the formula:

NonZero(
  ([Measures].[Issues created],
  [Resolution].[(unresolved)],
  [Time].[Last 6 months])
  -
  ([Measures].[Issues created],
  [Resolution].[(unresolved)],
  [Time].[Last 3 months])
)

best,
Gerda

1 Like