Getting only the first/lowest value from a multi select field

Questions & Answers
, , ,
1

Hello I’ve been racking my brain trying all sorts of things to try to solve this.

On our project we have the affects version dimension which can store multiple values.

This is great for seeing where the issue is occurring but not great when trying to report on when an issue was introduced.

The problem
When I go to use the Affects Version Dimension bugs will be counted multiple times across each affects version they have. Inflating capture stats and causing problems with escape calculations.

I only want issues to be counted for the lowest affects version when I do my other calculations in custom measure.

Question
Is there a way to have a custom dimension that only gives issues their lowest affect version value?

The report I’m trying to make would have the affects versions on the rows and my captured/escaped measures on the columns. the custom measures I’ve made are ‘working’ the problem is the affects version dimension.

From looking around on the Q&AsI suspect the best solution would be a javascript dimension based on the affects version field that pulls the lowest value only but I’m not really sure where to begin with that.

I’ve tried making calculated members the closest I got was on the issues dimension which caused other problems.

1 Like
2

Hi @Davo,
There are two approaches to getting this information.
First is using MDX calculation that iterates through issues, sorts the affected versions by release date, gets the earliest one, and counts the issue towards it. This calculation could lead to performance problems if you have a lot of issues in your data cube:

Sum(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    [Measures].[Issues created]> 0
    AND
    NOT IsEmpty([Measures].[Issue affects versions])
    ),
  CASE WHEN
    Order(
        Filter([Affects Version].[Version].Members,
        [Measures].[Issues created]>0
      ),
      [Affects Version].CurrentHierarchyMember.Get('Release date'),
      ASC
      --filter the report by the earliest version that matches the version selection in pages
    ).Item(0).Name = [Affects Version].CurrentHierarchyMember.Name
  THEN
    [Measures].[Issues created]
  END
)

The second approach is using a JavaScript custom field. Here is a solution for the “Latest affects version”:

You can transform the code and get the “Earliest affects version” using this field definition:

[jira.customfield_earliest_aversion]
name = "Earliest Affects Version"
data_type = "string"
dimension = true
javascript_code = '''
var versionList = [];
if (issue.fields.versions ) {
for (var i=0; i < issue.fields.versions.length; i++) {
  var versionName = issue.fields.versions[i].name;
  versionList.push(versionName);
}
}

if (versionList){
  issue.fields.customfield_earliest_aversion = versionList[0];
}
'''

Select the field for the import, and after the import, you will have a new dimension that holds only the “Earliest affects version” values.

best,
Gerda // support@eazybi.com

3

Thanks for your reply Greda!

So I eventually got to trying this by making a custom field the approach I used was the following:

[jira.customfield_lowestAffectsVersion]
name = "Lowest AV"
data_type = "string"
dimension = true
javascript_code = '''
if (issue.fields.versions) {
  issue.fields.lowestAffectsVersion = issue.fields.versions[0];
}
'''

The Java script code worked in test but not after setting up the field and doing the import.

I’ll try the above but was curious why my code worked in testing but not in practice :thinking:

4

Hi @Davo ,
This is because you need to use issue.fields.customfield_lowestAffectsVersion to assign the value to the field :slight_smile:
Try this configuration:

[jira.customfield_lowestAffectsVersion]
name = "Lowest AV"
data_type = "string"
dimension = true
javascript_code = '''
if (issue.fields.versions) {
  issue.fields.customfield_lowestAffectsVersion = issue.fields.versions[0];
}
'''

Whenever changing advanced settings for custom fields or issue links, you should perform double data import to ensure the correct outcome of changes (Advanced settings for custom fields).

You may do the following:

  1. In the import option, deselect the custom field “Lowest AV” from data import and import data. This action will clear the previous data and data structures.
  2. Wait for data import to complete.
  3. In the import option, select the custom field “Lowest AV” for data import and import data for the second time.

best,
Gerda

5

Ah I was soo close!

I will try this and do the double data import

Thank you!

1 Like