Measure in Jira according to Unique value in custom text and the max value of it

Questions & Answers
,
1

Hi Guys,

I have some challenge that I have been difficulties to solve in a calculated measure.
I have in my Jira Project one custom field that is Text type.
In this custom field you can have any customer name and each issue has another Custom field of number type.

I want to have a calculated measure, that per each unique Custom field of Customer type gives me Max Number on it, and then sum up this max number.

Example:

Issuetype (Customer Type) | Customer Name (BA Name) | Number (Consumption)
1 | Customer1 | 2
2 | Customer2 | 1
3 | Customer1 | 5
4 | Customer3 | 1
5 | Customer2 | 2
Total: 5+2+1 = 8

I use as dimension the Project, since I have multiple projects and I want to calculate by project.
The pseudo code in my head should be something like this:

Sum(
  Filter (Measure.IssueType = "Customer Type" 
  and Measure.Consumption> 0
  and Measure.BA Name is Unique),
Max(Measure.Consumption)
)

My Challenge is what exactly write in the filter set to apply those 3 conditions. The second thing is how to get the unique “BA Name”.

Thank you

2

You have two challenges here. To get a grouping by Customer name and then for each customer to retrieve the Max consumption value.

I would suggest importing Customer name as a dimension. eazyBI does not support importing text fields as dimensions by default. You would like to add this option to this field explicitly.

You would like to treat it with caution, though. Custom field values with manual data import do not have a limit by length. They could have different data entries (for example, the same value in different cases, for example, ABC, Abc, abc) it might lead to problems analyzing them correctly in eazyBI.

Anyhow, here is a custom field definition example, that overrides default setup by adding import option as a dimension:

   [jira.customfield_NNNNN] 
   data_type = "string" 
   dimension = true

Use the Jira custom field ID instead of NNNNN in the definition above.

Add the updated custom field definition to eazyBI advanced settings or ask Jira administrator or eazyBI administrator to do this for you. eazyBI advanced settings are common for all accounts and only Jira/eazyBI administrators have access to the settings.

Open import options for edit after changes in advanced settings and select the custom field for import as dimension and run an import.

After import, you should be able to use Customer name as a dimension in the report and use it for data analysis.

The next step is calculating a max consumption value for your issues.
You can use Function Max over a set of issues and address a particular custom field. I checked if there is any valu in the field using issue property and then used measure with created date as a parameter for Max function:

-- annotations.total=sum
Max(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    DateInPeriod(
      [Measures].[Issue created date],
      [Time].CurrentHierarchyMember
    )
    AND
    [Measures].[Issue type] = "Customer Type"
    AND
    not IsEmpty([Measures].[Issue Consumption])
  ),
  [Measures].[Consumption created]
)

In the formula above, I used annotation to sum values from rows for a total calculation explicitly. The solution should work for version 6.0.1.

Daina / support@eazybi.com

3

Hi Daina,

Thank you for reaching out!
Few things, First thank you for the tip around custom dimension, I have implemented it, and I think it will be needed even for future reports that I am building.
Currently I have installed the 6.0.1 version of EaziBI.

I have tried to put that formula with some adjustments to my particular business logic and I have now the following formula:

-- annotations.total=sum
Max(
    Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
    [Measures].[Consumption resolved]>0
  AND
    [Measures].[Issue type] = "Customer Type"
  ),
  [Measures].[Consumption resolved]
))

I don’t know if I need to adapt the annotations part somehow, but it seems that is the part failing on what I’m expecting to receive.

I have used as dimension: Project (jira Project) and the Customer Name (custom field).
If I expand my table I can see by Customer Name under each Project the Max of the Consumption Value.
I’m just missing the total Consumption of the Max consumption by project.

As an example:
Jira

I would want to see the total Consumption for all projects, then expanding by project I would like to see in each what is the number. In this case I see 2, which is the Max value that I currently. I would expect this 2, would sum up with all the other Max by Customer Type.
Just an ilustrative example. The Sum up shows 2, but I was expecting to count 3, just from the small snapshop.
Jira2

This indicates to me that I’m doing something wrong with annotations, since it seems the only missing part.

Thanks any additional help would be much appreciated.

4

Hi Daina,

Just checking if there is no easy solution for what I am trying to achieve without the annotation mark (that does not seem to work as expected).

5

The formula pulls out Max value from all resolved issues. I assumed you are using Total of rows instead of totaling member All Customers.

If you would like to use the measure in the report for a project not expanded to customers, you would like including the Sum over Customers within the formula:

Sum(Filter(
  Descendants([Customer Name].CurrentMember, [Customer Name].[Customer Name]),
  [Customer Name].CurrentMember.Name <> "(none)"),
  Max(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    [Measures].[Issue Customer Name] = [Customer Name].CurrentMember.Name
    AND
    DateInPeriod(
      [Measures].[Issue resolution date],
      [Time].CurrentHierarchyMember
    )
    AND
    [Measures].[Issue type] = "Customer Type"
    AND
    not IsEmpty([Measures].[Issue Consumption])
  ),
  [Measures].[Consumption resolved]
))

Please check if Customer Name is a correct name for the custom field (dimension and issue property) and update the formula if needed.

I used my original formula with some updates for a starter. I switched Created measure and property to resolved. I used issue properties as only filters to improve performance.

Daina / support@eazybi.com

6

Hi @daina.tupule,

Thank you for you follow up.
That formula showed up the sum max by my “BA Name” Custom field.
However I want to measure the formula expanded by Project, and now, if i try to expand it gives timeout, as you already mentioned.

image

If, I expand “All Projects” I will see 2 in some, 5 in others, nothing in others. Making the total of 53 as the image above.
If you can support me in explaining what is the tweak that I need to apply I would be much appreciated.

Thank you and apologies for being such a newbie :slight_smile:

7

The formula goes through a combination of all issues and all BA names. If you expand the report to the project level it will apply calculations (at least some validations) to any combination of all issues all BA names and all projects:
If you have 5 projects, 10 BA names, and 100 issues then the total calculations (at least some validations) will be 5 x 10 x 100 = 5000. I assume you have a lot more issues than 100. So the total amount of calculations might be a lot higher.

Any measure, in your case, Consumption resolved makes the main impact on performance. You would like to minimize a set of members to whom you apply a measure. You can use any property as filters to do so. Therefore, I used issue properties (any measure from the section Issue properties with a naming pattern starting with Issue) as filters only for the formula.

Here I added one more tweak, to retrieve issues of a particular project to support case when you expand this report to a project level.

Sum(Filter(
    -- all customers names 
  Descendants([Customer Name].CurrentMember, [Customer Name].[Customer Name]),
  [Customer Name].CurrentMember.Name <> "(none)"),
  Max(
  -- filter by issues: if expanded to project seection only issues in project will be picked up:
  Filter(
    Descendants(
    IIF([Project].CurrentMember.Level.Name = "Project", [Issue].[Project].GetMemberByKey([Project].CurrentHierarchyMember.Key),[Issue].CurrentMember), 
    -- expand to issue level
    [Issue].[Issue]),
    [Measures].[Issue Customer Name] = [Customer Name].CurrentMember.Name
    AND
    DateInPeriod(
      [Measures].[Issue resolution date],
      [Time].CurrentHierarchyMember
    )
    AND
    [Measures].[Issue type] = "Customer Type"
    AND
    not IsEmpty([Measures].[Issue Consumption])
  ),
  [Measures].[Consumption resolved]
  )
)

I expanded the same formula and used a different approach on how to access issues with function Descendants. Please replace the Customer Name with your correct dimension and property name in this code as well.

Daina / support@eazybi.com

8

Hi Daina,

Thank you again for your response. However using that formula with necessary changes for my use case, I didn’t get the expected outcome.
I have the measure “Daina V1” which is the formula in reply 5 and “Daina V2” which is the formula in reply 7.
image

You can see in the table above, that “Daina V1” is showing me the expected values, the only missing part is that if I try to expand the “All Projects” I always get timeout.
The V2 version at this point, allow me to expand the “All Projects” but is not doing the sum of each unique max value.

If I just expanded the “All Projects” I would expect to see 3 in the first project, because is the sum of the first 3 rows “all business application names” and the second project would have 2, which is the 4 row.

By the way, for now I am removing the condition of DateInPeriod, I still don’t have that much data that require me to add that additional condition.

Looking forward for your reply,

9

Yes, the formula had problems, I got lost in descendants and updated incorrect part of it.
I fixed the formula in my response (you referenced to it as reply 7) , so that the incorrect formula did not go into way.

Daina / support@eazybi.com

10

Thank you Daina!
That solved my formula and I can retrieve what I want.