Aggregating Affected Version

Questions & Answers
1

Trying to aggregate data using Affected Version so I can report all minor and major versions as one version release.
For example:
release 1.0 + release 1.1 + release 1.1.1 = Release 1
Release 2.0 + release 2.1 + release 2.2 = Release 2

Seen examples for Fix Version but they don’t work using Affected Version

2

Hi @ericwt64!

Sorry for the time you had to wait for a reply!

There are two ways you can do this

  1. You can Aggregate(…) the Fix version dimension members in the Fix version dimension based on the naming pattern.
  2. Another way, if you only need to sum one or some measures, you can create new calculated measures that dynamically find the first part of the Fix Version name and attach all other minor versions to it. Here is an example for the pattern you used in the example:
    Issues_-_eazyBI
    Issues_-_eazyBI1822×1208 201 KB

And here is the formula for that

    CASE WHEN [Fix Version].CurrentHierarchyMember.Name MATCHES ".*\.0" THEN -- when version name ends with .0
    Sum(Filter(
        [Fix Version.By status].[Version].Members, -- go through all versions in Status hierarchy
        [Fix Version.By status].CurrentMember.get("PARENT_KEY") -- find the versions from the same project (parent names match
          MATCHES [Fix Version].CurrentMember.get("PARENT_KEY") 
        AND
        [Fix Version.By status].CurrentMember.Name -- and the version name matches extension of current name
          MATCHES ExtractString([Fix Version].CurrentMember.Name, "(.*)\..*", 1) || ".*"
      ),
      ([Measures].[Issues created], [Fix Version].DefaultMember) -- here we sum issues created. This can be replaced with any other measure
    )
    END

Lauma / support@eazybi.com

3

Thanks Lauma. I was looking for a solution for Affected Version not Fix Version. I’m getting a null pointer exception when I change fix version to affected version.

4

@ericwt64,

Sorry for not noticing you were looking for Affects Version!
By replacing the Fix Version with Affects Version, you are very much on the right track. Unfortunately, Affects Version does not have a property PARENT_KEY to check that the version is from the same project, and this is where the formula fails.

Still, we can use Parent names for this check. Please try the following formula:

    CASE WHEN [Affects Version].CurrentHierarchyMember.Name MATCHES ".*\.0" THEN
    Sum(Filter(
        [Affects Version.By status].[Version].Members,
        [Affects Version.By status].CurrentMember.Parent.Name
          MATCHES [Affects Version].CurrentMember.Parent.Parent.Name
        AND
        [Affects Version.By status].CurrentMember.Name
          MATCHES ExtractString([Affects Version].CurrentMember.Name, "(.*)\..*", 1) || ".*"
      ),
      ([Measures].[Issues created], [Affects Version].DefaultMember)
    )
    END

Lauma / support@eazybi.com

5

Can someone help me to understand the syntax used in this example?

".*\.0"

And

"(.*)\..*", 1) || ".*"

I want to be able to name the versions something like Product A Release 1.0, Product A Release 1.1, etc

When I use the syntax in the example I can only pull Fix Versions with numbers only for the name (1.0, 1.1,1.2, etc)

I am sure this is an easy fix, I just don’t know the language very well

6

I was able to resolve this:

“.*\s\d.0”

and

“(.)..”, 1) || “.*”

respectively

7

How can I modify this to have differing release names though. For example

Beta Release 1.0
Alpha Release 1.1

^ Aggregating those

I tried this but it didnt work

CASE WHEN [Fix Version].CurrentHierarchyMember.Name MATCHES “.* Group \d+\.\d+” THEN
Sum(Filter(
[Fix Version.By status].[Version].Members,
[Fix Version.By status].CurrentMember.Parent.Name
MATCHES [Fix Version.By status].CurrentMember.Parent.Parent.Name
AND
[Fix Version.By status].CurrentMember.Name
MATCHES ExtractString([Fix Version].CurrentMember.Name, “.* Group (\d+\.\d+)”, 1) || “.*”
),
([Measures].[Cumulative Story Points resolved], [Fix Version].DefaultMember)
)
END

8

Hi @Jeppley ,

Can you please give a screenshot of which versions you would like to sum? How are they grouped in the Fix Version dimension now?
Do you mean that no matter what is the prefix string, 1.0 and 1.1 Story points resolved should be summed together?

Lauma / support@eazybi.com

9

@lauma.cirule That is exactly what I mean, that no matter what is the prefix string, 1.0 and 1.1 story points resolved should be summed. Here’s an example of the Fix Version names I am trying to sum: “PPAP2 FBM 1.0”, “P1 Build - FBM 1.1”, “P2 Build FBM 1.2”, “C3 Durability Failure 1.6”

Quite simply, I want to be able to add more context to the Version name than just “1.0”, “1.1”, “1.2”, etc.

Here’s a screenshot of my real versions:
image

10

Hi @Jeppley,

I see that the pattern is that the number of versions is at the end of the name. In such a case, we can construct the regex in a way that we add any symbol (.*) at the beginning of the matching string and then find the number before .; then add that there is any number behind the dot :

".*" || ExtractString([Fix Version].CurrentMember.Name, "(\d+)\.\d+$", 1) || "\.\d+"

Lauma / support@eazybi.com