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
Hi @ericwt64!
Sorry for the time you had to wait for a reply!
There are two ways you can do this
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
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.
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
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
I was able to resolve this:
“.*\s\d.0”
and
“(.)..”, 1) || “.*”
respectively
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
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
@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:
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
@lauma.cirule Would there be any way to aggregate/sum multiple versions without a unique naming convension? For example simply by selecting multiple versions in the pages?
When i do this now, it breaks:
Report definition:
{
"cube_name": "Issues",
"cube_reports": [ {
"name": "All Projects Configurable Jira Project Prediction report",
"folder_name": "CoC BC",
"result_view": "timeline_chart",
"definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[Project prediction]","[Measures].[Project prediction low]","[Measures].[Project prediction high]","[Measures].[Rolling prediction]","[Measures].[Story Points history]","[Measures].[Cumulative Story Points resolved till today]","[Measures].[Selection Predicted Date]","[Measures].[Current Date]","[Measures].[Version Commited Date]"],"members":[]}]},"rows":{"dimensions":[{"name":"Time","selected_set":["[Time.Weekly].[Week].Members"],"members":[],"bookmarked_members":[]},{"name":"Assignee","selected_set":["[Assignee].[All Assignees]"],"members":[],"bookmarked_members":[]},{"name":"Issue Type","selected_set":["[Issue Type].[All Issue Types]"],"members":[],"bookmarked_members":[]},{"name":"Fix Version","selected_set":["[Fix Version].[All Fix Versions]"],"members":[],"bookmarked_members":[]}],"filter_by":{"conditions":[{"expression":["[Measures].[Time in project]"],"operator":"\u003e","value":"0"}]},"nonempty_crossjoin":false},"pages":{"dimensions":[{"name":"Project","selected_set":["[Project].[All Projects]"],"members":[{"depth":0,"drillable":true,"drilled_into":false,"expanded":true,"full_name":"[Project].[All Projects]","name":"All Projects","type":"all"}],"bookmarked_members":[],"current_page_members":["[Project].[All Projects]"]},{"name":"Fix Version","duplicate":true,"selected_set":["[Fix Version].[All Fix Versions]"],"members":[{"depth":0,"name":"All Fix Versions","full_name":"[Fix Version].[All Fix Versions]","drillable":true,"type":"all","expanded":true,"drilled_into":false}],"bookmarked_members":[],"current_page_members":["[Fix Version].[All Fix Versions]"]}]},"options":{},"view":{"current":"timeline_chart","maximized":false,"timeline_chart":{"series_type":"line","series_options":{"All open story points":{"type":"line-Dash"},"Cumulative Issues resolved":{"color":"#00875A","type":"area"},"Dates":{"dataLabelType":"top vertical","type":"plotline"},"Milestones":{"dataLabelType":"top vertical","type":"plotline"},"Predicted date":{"dataLabelType":"top vertical","type":"plotline"},"Predicted date new":{"dataLabelType":"top vertical","type":"plotline"},"Predicted on":{"dataLabelType":"top vertical","type":"plotline"},"Project prediction":{"type":"line-Dash"},"Project prediction high":{"color":"#FFBC29","type":"line-Dash"},"Project prediction low":{"color":"#FFBC29","type":"line-Dash"},"Rolling prediction":{"type":"line-Dash"},"Today":{"color":"#5E6C84","dataLabelType":"top vertical","type":"plotline"},"Total story points":{"type":"line-Dash"},"Version burn-down":{"type":"line-Dash"},"Version burn-up":{"type":"line-Dash"},"S-curve guideline":{"color":"#E74B71","type":"line-Dash"},"Cumulative Story Points resolved":{"color":"#00875A","type":"area"},"Cumulative Story Points resolved till today":{"color":"#00875A","type":"area"},"Selection Predicted Date":{"type":"plotline","dataLabelType":"top vertical","color":"#00B8D9"},"Current Date":{"type":"plotline","color":"#5E6C84","dataLabelType":"top vertical"},"Version Commited Date":{"type":"plotline","color":"#B31238","dataLabelType":"top vertical"}},"show_full_caption":["Measures"]},"table":{}},"calculated_members":[],"description":"\u003cspan style=\"font-family:Papyrus; font-size:1.5em;\"\u003eSelect only a **SINGLE Project** and/or a **SINGLE Version**\u003c/span\u003e\n\u003cspan style=\"font-family:Papyrus; font-size:1.25em;\"\u003eHow is your experience? \u003ca href=\"https://forms.office.com/e/9tcyj9B3Xh\"\u003eLeave feedback on this tool here\u003c/a\u003e.\u003c/p\u003e\u003c/span\u003e"}
} ],
"calculated_members": [{"name":"Story Points history","dimension":"Measures","format_string":"#,##0.00","formula":"Cache(\n NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),\n [Measures].[Story Points change]\n ))\n + [Measures].[Story Points change]\n)\n"},{"name":"Cumulative Story Points resolved","dimension":"Measures","format_string":"#,##0.00","formula":"Cache(\n Sum({PreviousPeriods([Time].CurrentHierarchyMember),\n [Time].CurrentHierarchyMember},\n [Measures].[Story Points resolved]\n )\n)\n"},{"name":"Story Points remaining","dimension":"Measures","format_string":"#,##0.00","formula":"[Measures].[Story Points history] -\n[Measures].[Cumulative Story Points resolved]\n"},{"name":"Cumulative Story Points resolved till today","dimension":"Measures","formula":"Case when\n -- show any past periods\n DateAfterPeriodEnd(\n \"Today\",\n [Time].CurrentHierarchyMember)\n OR \n -- show current period\n DateInPeriod(\n \"Today\",\n [Time].CurrentHierarchyMember\n )\nThen\n[Measures].[Cumulative Story Points resolved]\nEnd","format_string":""},{"name":"Project predicted date","dimension":"Measures","formula":"--annotations.group= Predicted by issues\nCache(\nCase when\n-- there is already resolved \n ([Measures].[Story Points resolved],\n [Time].CurrentHierarchy.DefaultMember) \u003e 0\n AND\n-- there is still some unresolved issues\n ([Measures].[Story Points remaining], \n [Time].CurrentHierarchy.DefaultMember) \u003e 0\nThen\n DateAddDays( 'Today',\n -- add days to reach this ammount from Now till..\n -- in how many days you will resolve currently open issues based on \n -- previously resolved issues from project start till today\n Cache(\n -- all currently open issues\n ([Measures].[Story Points remaining], \n [Time].CurrentHierarchy.DefaultMember)\n /\n -- divided by days performance\n Cache(\n -- currently resolved issues\n ([Measures].[Story Points resolved], \n [Time].CurrentHierarchy.DefaultMember)\n /\n -- days till today from project start\n DateDiffDays(\n [Measures].[Project start date], \n 'Today')\n )\n ) + 1\n)\nEnd)","format_string":"mmm dd yyyy"},{"name":"Project start date","dimension":"Measures","formula":"--annotations.group= Predicted by issues\n-- projects starts at the moment when the first issue is resolved from any In progress status\nDateWithoutTime(TimestampToDate(\n ([Measures].[Transition from first timestamp],\n [Resolution].[(unresolved)],\n [Transition Field].[Resolution],\n [Transition status.Category].[In Progress],\n [Time].CurrentHierarchy.DefaultMember)\n))\n-- old version\n-- Cache(\n-- -- First date with issues resolved will be project Start date\n-- Filter([Time].CurrentHierarchy.Levels('Day').Members,\n-- [Measures].[Issues resolved] \u003e 0\n-- ).Item(0).StartDate \n-- )\n\n","format_string":"mmm dd yyyy"},{"name":"Project prediction","dimension":"Measures","formula":"--annotations.group=Predicted by issues\nCASE WHEN\n-- apply for predicted period only\n (DateinPeriod(\n [Measures].[Project start date], \n [Time].CurrentHierarchyMember)\n OR\n DateBetween([Time].CurrentHierarchyMember.StartDate, \n [Measures].[Project start date],\n [Measures].[Project predicted date]))\nTHEN\n -- pace of currently resolved points per day : total resolved days in version divided by days in version till today\n Cache(\n ([Measures].[Story Points resolved], \n [Time].CurrentHierarchy.DefaultMember)\n /\n DateDiffDays(\n [Measures].[Project start date], \n DateParse('Today'))\n )\n *\n -- calculate progress for each period from start date till period end or till predicted date\n Case \n when\n DateInPeriod([Measures].[Project predicted date],[Time].CurrentHierarchyMember) \n then\n DateDiffDays(DateAddDays([Measures].[Project start date],1),[Measures].[Project predicted date]) + 1 \n else \n DateDiffDays(DateAddDays([Measures].[Project start date],1),[Time].CurrentHierarchyMember.NextStartDate)\n End\nEND","format_string":"#,##0.00"},{"name":"Time in project","dimension":"Measures","formula":"--annotations.group= Predicted by issues\nCase when\n DateBetween(\n [Time].CurrentHierarchyMember.StartDate,\n [Measures].[Project start date],\n [Measures].[Project predicted date]\n )\n or\n DateInPeriod(\n [Measures].[Project start date],\n [Time].CurrentHierarchyMember\n )\n Then\n 1\n End","format_string":"#,##0"},{"name":"Project prediction high","dimension":"Measures","formula":"--annotations.group=Predicted by issues\nCASE WHEN\n-- apply for predicted period only\n (DateinPeriod(\n 'Today', \n [Time].CurrentHierarchyMember)\n OR\n DateBetween([Time].CurrentHierarchyMember.StartDate, \n 'Today',\n [Measures].[Project predicted date]))\nTHEN\n -- pace of currently resolved points per day : total resolved days in version divided by days in version till today\n [Measures].[Cumulative Story Points resolved]\n +\n Cache(\n ([Measures].[Story Points resolved], \n [Time].CurrentHierarchy.DefaultMember)\n /\n DateDiffDays(\n [Measures].[Project start date], \n DateParse('Today'))\n )\n -- high + 25%\n * 1.25\n *\n -- calculate progress for each period from Today till period end or till predicted date\n Case \n when\n DateInPeriod([Measures].[Project predicted date],[Time].CurrentHierarchyMember) \n then\n DateDiffDays('Tomorrow',[Measures].[Project predicted date]) + 1 \n else \n DateDiffDays('Tomorrow',[Time].CurrentHierarchyMember.NextStartDate)\n End\nEND","format_string":"#,##0.00"},{"name":"Rolling prediction","dimension":"Measures","formula":"--annotations.group=Predicted by issues\nCase\nwhen\n DateInPeriod(\n 'Today',[Time].CurrentHierarchyMember)\nthen\n NonZero((\n [Measures].[Story Points resolved],\n [Time].CurrentHierarchy.DefaultMember))\nWhen\n DateBetween(\n [Time].CurrentHierarchymember.StartDate,\n 'Today',\n -- predicted date by rolling period\n [Measures].[Rolling predicted date]\n )\n Then\n -- current progress\n NonZero((\n [Measures].[Story Points resolved],\n [Time].CurrentHierarchy.DefaultMember))\n +\n -- add pace for each day in future\n (AVG(\n {[Time.Weekly].[Week].CurrentDateMember.Lag(52): -- should match the weeks in Rolling predicted date measure\n [Time.Weekly].[Week].CurrentDateMember.PrevMember},\n CoalesceEmpty(\n ([Measures].[Story Points resolved],\n [Time].DefaultMember),\n 0))\n -- workdays per week\n / 5 )\n -- axpand one of those two lines for additional scenarious\n -- * 1.20 -- for best case \n -- * 0.80 -- for worse case\n *\n DateDiffWorkDays('Tomorrow',[Time].CurrentHierarchyMember.NextStartDate)\nEnd","format_string":"#,##0.00"},{"name":"Rolling predicted date","dimension":"Measures","formula":"-- annotations.group = Predicted by issues\nCache(CASE WHEN\n Cache(AVG(\n {[Time.Weekly].[Week].CurrentDateMember.Lag(52):\n [Time.Weekly].[Week].CurrentDateMember.PrevMember},\n ([Measures].[Story Points resolved],\n [Time].DefaultMember)\n )) \u003e 0\n AND\n ([Measures].[Story Points remaining],\n [Time].CurrentHierarchy.Defaultmember) \u003e 0\nTHEN\n DateAddWorkDays(\"Today\", \n -- remaining scope\n ([Measures].[Story Points remaining],\n [Time].CurrentHierarchy.Defaultmember)\n / \n -- average resolved issues per week in last 52 weeks\n Cache(AVG(\n {[Time.Weekly].[Week].CurrentDateMember.Lag(52):\n [Time.Weekly].[Week].CurrentDateMember.PrevMember},\n CoalesceEmpty(\n ([Measures].[Story Points remaining],\n [Time].DefaultMember),\n 0)\n )) \n * \n -- workdays in week\n 5\n -- expand to see either worse or best case\n -- / 1.20 -- for best case \n -- / 0.80 -- for worse case\n )\nEND)","format_string":"mmm dd yyyy"},{"name":"Current Date","dimension":"Measures","formula":"--annotations.group=Predicted\nCase When\n DateInPeriod(\n Now(),\n [Time].CurrentHierarchyMember)\nThen\n 'Today - ' \n || + Format(Now(), 'Medium Date')\nend","format_string":"#,##0.00"},{"name":"Project prediction low","dimension":"Measures","formula":"--annotations.group=Predicted by issues\nCASE WHEN\n-- apply for predicted period only\n (DateinPeriod(\n 'Today', \n [Time].CurrentHierarchyMember)\n OR\n DateBetween([Time].CurrentHierarchyMember.StartDate, \n 'Today',\n [Measures].[Project predicted date]))\nTHEN\n -- pace of currently resolved points per day : total resolved days in version divided by days in version till today\n [Measures].[Cumulative Story Points resolved]\n +\n Cache(\n ([Measures].[Story Points resolved], \n [Time].CurrentHierarchy.DefaultMember)\n /\n DateDiffDays(\n [Measures].[Project start date], \n DateParse('Today'))\n )\n -- low -25%\n * 0.75\n *\n -- calculate progress for each period from today till period end or till predicted date\n Case \n when\n DateInPeriod([Measures].[Project predicted date],[Time].CurrentHierarchyMember) \n then\n DateDiffDays('Tomorrow',[Measures].[Project predicted date]) + 1 \n else \n DateDiffDays('Tomorrow',[Time].CurrentHierarchyMember.NextStartDate)\n End\nEND","format_string":"#,##0.00"},{"name":"Selection Predicted Date","dimension":"Measures","formula":"--annotations.group=Predicted by issues\nCase \nWhen \n DateInPeriod(\n [Measures].[Project predicted date],\n [Time].CurrentHierarchyMember) \nThen\n Case when\n DateInPeriod(\n [Fix Version].CurrentHierarchymember.Get('Release date'),\n [Time].CurrentHierarchyMember)\n Then\n 'Predicted date - ' \n || Format([Measures].[Project predicted date], 'Medium Date')\n Else\n 'Predicted date - ' \n || Format([Measures].[Project predicted date], 'Medium Date')\n End\nEnd","format_string":"#,##0.00"},{"name":"Version Commited Date","dimension":"Measures","formula":"--annotations.group=Predicted by issues\nCase \nWhen\n DateInPeriod(\n [Fix Version].CurrentHierarchymember.Get('Release date'),\n [Time].CurrentHierarchyMember) \nThen\n 'Commitment - ' \n || + Format([Fix Version].CurrentHierarchymember.Get('Release date'), 'Medium Date')\nend","format_string":""}]
}
Hi Jeppley,
The report breaks because the Timeline expects only one dimension on rows - the Time. Adding any other split (e.g., Fix Version) on the row dimension ‘splits’ the Time. I would suggest you leave the Fix Version (and other row dimensions) only as a filter for pages and remove them from the rows.
Selecting multiple versions will require some rewriting of calculations where I noticed you are looking at the fix version’s current member, e.g., `[Fix Version].CurrentHierarchymember.Get(‘Release date’), but there is no release date for multiple selections. You would need to think of logic for how to get this date (the first or last release date from the selected) or display a line for each version chosen. For example, all version release lines in this report: Versions on Timeline - Issues - Jira Demo - eazyBI.
Lauma / support@eazybi.com