Help on creating eazybi mdx measure

Questions & Answers
1

Hi,

Can you please help me on creating new mdx measure for below jql query

jql : “DE - Country Review” in (“In Progress”, Planned) AND “DE - Completion Due Date (F2C)” < endOfDay()

DE - Country Review is a custom field and In progress and planned are values and DE - Completion is a date field.

I want to see the issues which are less than end of day in DE - Completion Due Date (F2C)

2

Hi @Amzad

First of all, please import the “DE - Country Review” custom field as a dimension and a property, and the “DE - Completion Due Date (F2C)” as a measure and a property.

After this, go to your report, and in the DE - Country Review dimension, define a new calculated member “Planned Progress” with a formula like this:

Aggregate({
  [DE - Country Review].[In Progress],
  [DE - Country Review].[Planned]
})

Next, you can define a new calculated measure in the Measures dimension with a formula like this:

Sum(
  PreviousPeriods(
    [Time].[Day].DateMember('tomorrow')
  ),
  (
    [Measures].[Issues with DE - Completion Due Date (F2C)],
    [DE - Country Review].[Planned Progress]
  )
)

​Best regards,
​Nauris

3

Thanks for your reply it helped.

Can you please also help me to build report and another mdx measure for below
If “UK Due Date” is passed compared to “current date” and “Country Review UK” is not in custom field value “Accepted as-is” or “accepted with change” need to show Sum.

UK Due date is a date field
Country Review UK is a custom field with values Accepted As is and Accepted with change and others.

I need to show the sum of issues which are passed current date in “UK due date” date field, which are not in values Accepted as is and accepted with change in custom field Country Review UK

Below jql I wanted to get the report

project = CDS and “Country Review UK[Dropdown]” not in (“Accepted - Use As Is”, “Accepted - Use with Change”) and “UK - Completion Due Date (F2C)[Date]” < now()

Can you please also help me on the same.

4

CASE WHEN
[Time].CurrentHierarchyMember is [Time].CurrentHierarchy.DefaultMember
THEN
SUM(
PreviousPeriods(
[Time].CurrentHierarchy.Levels(“Day”).CurrentDateMember),
[Measures].[Issues with UK - Completion Due Date (F2C)]

)
WHEN
DateAfterPeriodEnd(“Today”, [Time].CurrentHierarchyMember)
OR
DateInPeriod(“Today”, [Time].CurrentHierarchyMember)
THEN
SUM(
PreviousPeriods(
[Time].CurrentHierarchyMember),
[Measures].[Issues with UK - Completion Due Date (F2C)]
)
END

I have tried using the above measures and was able to get the Issues with UK - Completion Due date (F2C), I need help where I wanted to create a measure where I can filter two values “Accepted - Use As is” and “Accepted - use with change” of a custom field “Country Review UK” in the same measure and get the same number rather than using any filters in the report.

5

Hi @Amzad

If you have imported the “Country Review UK” custom field as a dimension, then you should be able to use a tuple for the measure expression in the Sum() function.

In the formula, instead of this:

SUM(
  PreviousPeriods([Time].CurrentHierarchyMember),
  [Measures].[Issues with UK - Completion Due Date (F2C)]
)

You can use this:

SUM(
  PreviousPeriods([Time].CurrentHierarchyMember),
  (
    [Measures].[Issues with UK - Completion Due Date (F2C)],
    [Country Review UK].[Accepted - Use As is]
  )
)

If you want to use both of the Accepted members, you can define a new calculated member in the Country Review UK dimension with a formula like this:

Aggregate({
  [Country Review UK].[Accepted - Use As is],
  [Country Review UK].[Accepted - use with change]
})

And then reference this measure in the Sum() formula.

​Best regards,
​Nauris

6

Hi,
The above helped if I only use it for one column.
As you can see in the below attached screenshot.
When I try to do the same for the other columns the data is not correct.
image

In the below measure can you please help me to create an aggregate of values "In Progress, Out of Scope, Planned in the below.

I have tried couple of things, but was unsuccessful.

I want to get issues Overdue in each column only the issues in the values In Progress, Out Of scope, and planned. I want to exclude the others.

CASE WHEN
[Time].CurrentHierarchyMember is [Time].CurrentHierarchy.DefaultMember
THEN
SUM(
PreviousPeriods(
[Time].CurrentHierarchy.Levels(“Day”).CurrentDateMember),
[Measures].[Issues with UK - Completion Due Date (F2C)]

)
WHEN
DateAfterPeriodEnd(“Today”, [Time].CurrentHierarchyMember)
OR
DateInPeriod(“Today”, [Time].CurrentHierarchyMember)
THEN
SUM(
PreviousPeriods(
[Time].CurrentHierarchyMember),
[Measures].[Issues with UK - Completion Due Date (F2C)]
)
END

7

Hi Amzad,

You can define a new calculated member “Progress, Scope, Planned” in the Status dimension with a formula like this:

Aggregate({
  [Status].[In Progress],
  [Status].[Out Of scope],
  [Status].[planned]
})

Next, you can use this member in the previous tuple in a measure in the Measures dimension:

SUM(
  PreviousPeriods([Time].CurrentHierarchyMember),
  (
    [Measures].[Issues with UK - Completion Due Date (F2C)],
    [Country Review UK].[Accepted - Use As is],
    [Status].[Progress, Scope, Planned]
  )
)

​Best regards,
​Nauris