I have a field - Project Number. The field contains project numbers in the form of DCI-1234 and FC-1234.
I need to count the number of times DCI occurs and the number of times FC occurs. I suspect I will need to use a regular expression.
Any suggestions?
Hi @rheigl,
Welcome to the eazyBI community!
There are a few possible approaches based on your setup.
If you have imported this field as a property of the issues, you might iterate through the Issue dimension and then count the occurrence of the field via regular expression. The main problem is that iteration through the Issue dimension might be slow in instances with a high number of issues.
If you have a relatively lower number of “Project number” options and have imported this field as a dimension, then you might create calculated members within the “Project number” dimension using regular expression for filtering the members.
However, if you have a high number of issues and a high number of “Project number” options, you might create a JavaScript-calculated customfield dimension that evaluates the contents of the field upon data import. Then you might address them via tuples.
The expression for the iteration through the Issue dimension might be as follows.
Sum(
Filter(
DescendantsSet(
[Issue].CurrentHierarchyMember,
[Issue].[Issue]),
--related to the report context
[Measures].[Issues created]>0
AND
--regular expression to check the contents
[Issue].CurrentHierarchyMember.Get('Project number') MATCHES "^DCI.*"),
--numeric value for sum
[Measures].[Issues created]
)
The next option if you import the Project number as a dimension. This might work faster as there are fewer “Project numbers” than issues. However, this only works until 1000 members for aggregation.
Please read more about defining calculated members here - Calculated members in other dimensions.
The expression for the calculated member “all DCI” might be as follows.
Aggregate(
Filter(
[Project numbers].[Project numbers].Members,
[Project numbers].CurrentHierarchyMember.Name MATCHES "^DCI.*")
)
Then you might use the following tuple to retrieve the number of related issues.
([Measures].[Issues created],
[Project numbers].[all DCI])
However, if you have a rather large instance with multiple issues and many options for “Project number”, you might define a new calculated field via import settings.
Please read more about defining new calculated fields here - New calculated fields.
The Javascript code for the field might be as follows.
if (issue.fields.customfield_NNNNN){
if (issue.fields.customfield_NNNNN.match("^(DCI|FC)")){
return issue.fields.customfield_NNNNN.match("^(DCI|FC)")[0];
}
}
After importing this dimension, you might address it via tuples.
Regards,
Oskars / support@eazyBI.com
Hi @oskars.laganovskis ,
I have a custom field where if that i need to calcualate the count of a text that appears in a row and assign a value.
For example,
I have data like this
I need to do a calculation like i should have a separate column at the last that should do calculation and provide points based on the below
For every green 1 point
For every yellow or Red -1 Point.
So the resultant column should
if there are 2 green and 1 yellow the result should be 1
If there are 1 green and 3 red the result column shouls be -2.
Like this i need to get for all the rows. Is it possible ?
