2 Questions: If Then query for string & Age Bucket

Questions & Answers
2

Hi @terrance

Welcome to eazyBI Community!
Congratulations on learning a new “language.” I know the beginning is always confusing and overwhelming, but I assure you—the more you use and practise it, the more sense it will make. :student:

For your first question - you have the right logic. Except in MDX we can use either IIF statement (IIf statement) or CASE statement (CASE statement), In your example, you have tried to mix both of them, which is why it wouldn’t work. I have just responded another Community post here - Hours Spent calculated member with formula based on different projects - #2 by Elita.Kalane with an example with CASE statement.

For second question - there are a few ways how to approach this.
The easiest would be using Age Interval (learn more here - Interval dimensions ). Please check documentation here - (Data from Jira) on how to import the Age interval if you haven’t already imported it. This would help you to split easily the report into Age intervals for unresolved issues. In your case, the report might look like this:

Add Age Interval dimension in Rows and edit the interval similarly as I have shown in the screenshot below. It would create the intervals I have marked in yellow.

2190×1560 257 KB

To create the age bracket for Less than 60 days (technically, this would mean all tickets open from 0 to 59.999days), define a new calculated member in Age Interval dimension with formula below. You can learn more about defining new members here - Calculated members in other dimensions :

Aggregate(
    {[Age interval].[00 - 29],
    [Age interval].[30 - 59]}
  )

2648×1314 319 KB

Using “Search and Bookmark functionality”, find the other two members - “60 - 89” and " 90 - …"
Then select all three members in the report. Note - select them in the sequence as you would like them to be displayed in the report. You can learn more about “search and bookmark” here - Bookmark a Release member - #2 by martins.vanags It is an example on a different dimension, however, “search and bookmark” works the same across all dimensions

1522×882 76.3 KB

Otherwise if you don’t want to use Age Interval and you would simply like to compare the dates if you have issues in rows, you should use DateDiffDays function - DateDiffDays. Here is a formula that would return “True” if creation date compared to Today is less or equal to 60 days. If you only want to look at workdays, replace DateDiffDays with DateDiffWorkdays

DateDiffDays(
  [Measures].[Issue created date],
  Now()
) <=60

If you have any questions, let us know either here or by contacting us directly at support@eazybi.com

Best wishes,

Elita from support@eazybi.com

1 Like