S-Curve Based on Weighted Status

Questions & Answers
1

I’m working on a request to create S-Curve based on summing the status of issues within scope, where the status is converted to a weight. I started with ChatGPT and it produced this. I provided the weightings but some rational weighting of any workflow would work when explaining how to do it. I saw the S-Curve examples and that helps with the possibility of creating the ideal curve.
With various attempts to get a set of measures I seem to get a result of 0 or the calculation timing out.

  1. Define the Weighted Status Calculation:
    In EasyBI, define a calculated measure to assign weights based on the status history for each issue over time. This measure will sum the weights according to the status of each issue within each time period.
    [Measures].[Status Weight] =
    CASE
    WHEN [Status].[Status].CurrentMember.Name = “Reopened” THEN 0.0
    WHEN [Status].[Status].CurrentMember.Name = “In Review” THEN 0.05
    WHEN [Status].[Status].CurrentMember.Name = “Refining” THEN 0.1
    WHEN [Status].[Status].CurrentMember.Name = “Ready” THEN 0.15
    WHEN [Status].[Status].CurrentMember.Name = “On Hold” THEN 0.2
    WHEN [Status].[Status].CurrentMember.Name = “In Progress” THEN 0.3
    WHEN [Status].[Status].CurrentMember.Name = “Test Ready” THEN 0.5
    WHEN [Status].[Status].CurrentMember.Name = “Testing” THEN 0.7
    WHEN [Status].[Status].CurrentMember.Name = “Ready For Release” THEN 0.95
    WHEN [Status].[Status].CurrentMember.Name = “Closed” THEN 1.0
    ELSE 0 – Default weight if status does not match
    END

  2. Sum Weight Over Time for the S-Curve:
    Using the weekly or periodic status history, sum these weights across all issues to create a cumulative measure.
    [Measures].[Cumulative Status Weight] =
    SUM(
    [Issue].[Issue].Members,
    [Measures].[Status Weight] * [Measures].[Issues history]
    )

  3. Plot Cumulative Measure Against Time:
    Create the S-Curve by plotting the cumulative measure (summed weight) against time.
    I converted the latter into Pages, Rows and Columns
    SELECT
    {[Measures].[Cumulative Status Weight]} ON COLUMNS,
    [Time].[Weekly].Members ON ROWS
    FROM [Issues]
    WHERE
    ([Project].[MYJIRAKEY], [Issue Type].[MYISSUETYPE])

ChatGPT Explanation of the MDX Query:

  • Status Weight: This custom member uses a CASE statement to assign the weight based on each issue’s status.
  • Cumulative Status Weight: This measure accumulates the weighted progress over time by summing up the weighted values of statuses across all issues for each time period.
  • Weekly Members: Plots the data on a weekly basis. Adjust to other time periods as needed.
    This will result in a cumulative graph where you can see the weighted progress over time, forming an S-Curve.

This measures resulted in a timeout after 120s

2

Hi,

The weighted issue history should be possible to implement with a more efficient formula.

Please check if the following measure works as expected:

([Measures].[Issues history],
[Transition Status].[In Review])*0.05
+
([Measures].[Issues history],
[Transition Status].[Refining])*0.1
+
([Measures].[Issues history],
[Transition Status].[Ready])*0.15
+
([Measures].[Issues history],
[Transition Status].[On Hold])*0.2
+
([Measures].[Issues history],
[Transition Status].[In Progress])*0.3
+
([Measures].[Issues history],
[Transition Status].[Test Ready])*0.5
+
([Measures].[Issues history],
[Transition Status].[Testing])*0.7
+
([Measures].[Issues history],
[Transition Status].[Ready For Release])*0.95
+
([Measures].[Issues history],
[Transition Status].[Closed])

Kindly,
Janis, eazyBI support

3

I think that’s it! The AI bots won’t replace us after all. Thank you!

1 Like