1. seuqra-atlassian-add-ons Avatar seuqra-atlassian-add-ons
  2. Seuqra Atlassian Add-ons
  3. confluence-add-ons
  4. Issues

Pivot macro doesn't sum remaining estimate.

Issue #2 resolved
Doug Robinson created an issue

I would like to sum the values in the 'original estimate' and 'remaining estimate' table for a set of JIRA issues, yet the macro doesn't seem to be able to add up these values (result is always "0"). it seems to work with numeric custom fields for the same set of JIRA issues, so I think I'm using the macro correctly.

help?

Comments (18)

  1. Xavier Arques

    Doug

    Can you confirm that the estimate fields contain text like "2 days, 2 hours" or "4 days". Actually, Pivot Table macro is trying to convert the content of a cell to a number before summing them. It works fine with values string like "4.5" but not with "2 days, 2 hours".

    If you confirm the above, I will try to implement a conversion algorithm from "x days, y hours" to a number of hours or minutes. But it will depend on the user language used in Jira/Confluence so not sure it will be generic enough

    Xavier

  2. Doug Robinson reporter

    Xavier,

    Thanks for getting back to me… ! Here is the time format in JIRA for our issues.. If you export the issue list, the time is outputted as ‘seconds’. I don’t see where in JIRA to change this formatting. If you know how/where, I don’t mind changing it on my end.

    [cid:image001.jpg@01D15388.D0846410]

    Here’s a screenshot from a JIRA Pivot gadget we’re using, you can see that it seems to refer to the above formatting as “pretty print”. But in any case, it is able to sum the values that JIRA stores in the “remaining estimate” field: [cid:image003.png@01D15388.61D87B50]

    Final report … first as “pretty print”, second as “hours” [cid:image008.png@01D15388.900C06E0] [cid:image004.png@01D15388.D0768160]

  4. Xavier Arques

    Doug,

    Here is my proposal: I will add a regexp field in the macro which will be associated to the Values parameter. This field will contain a regular expression and a replacement string. The replacement string can be a formula. The result will be a number of hours that cannot be transformed to the initial format.

    For example, if 1 day = 5 hours and a first value contains 1 day, 2 hours and a second value contains 2 days, 3 hours, the sum will be 

    (1 * 5 + 2) + (2 * 5 + 3) = 20

    Examples:

    regexp = "(((\\d*) week){0,1}(s){0,1}(, ){0,1}((\\d*) day){0,1}(s){0,1}(, ){0,1}((\\d*) hour){0,1}(s){0,1}(, ){0,1})+"
replacement = "(0$3 * 5 * 5) + (0$7 * 5) + 0$11"

    Results:

    1 week, 1 day, 1 hour replaced by (01 * 5 * 5) + (01 * 5) + 01 = 31.0
1 week, 2 days, 3 hours replaced by (01 * 5 * 5) + (02 * 5) + 03 = 38.0
2 weeks, 2 days, 3 hours replaced by (02 * 5 * 5) + (02 * 5) + 03 = 63.0
2 days, 3 hours replaced by (0 * 5 * 5) + (02 * 5) + 03 = 13.0
1 week, 2 days replaced by (01 * 5 * 5) + (02 * 5) + 0 = 35.0
1 week, 3 hours replaced by (01 * 5 * 5) + (0 * 5) + 03 = 28.0
1 week replaced by (01 * 5 * 5) + (0 * 5) + 0 = 25.0
2 weeks replaced by (02 * 5 * 5) + (0 * 5) + 0 = 50.0
1 day replaced by (0 * 5 * 5) + (01 * 5) + 0 = 5.0
2 days replaced by (0 * 5 * 5) + (02 * 5) + 0 = 10.0
1 hour replaced by (0 * 5 * 5) + (0 * 5) + 01 = 1.0
3 hours replaced by (0 * 5 * 5) + (0 * 5) + 03 = 3.0

    In the regexp, you can change week, day, hour by any other string and in the replacement string, you can adjust the multiplier value depending on the Jira configuration

    Is that going to work for you ?

    Xavier

  5. Doug Robinson reporter

    Xavier,

    While I don’t have a background in regular expressions, I think this look great!

    Thank you for your support, I’m looking forward to trying it.

    Regards, Doug

  6. Xavier Arques

    Doug,

    Do you have a Confluence instance on which you could test a beta version before I publish it on the Marketplace ? I think I can have a new version by Sunday evening.

    Can you also give me a set of Original Estimate values you have in your table.(with different combinations between weeks, days, hours, ...)

    Regards Xavier

  7. Doug Robinson reporter

    Xavier,

    Yes, I have your macro currently running in our instance….here’s the screenshot, showing the supporting table:

    I’ve recently set our JIRA time format to hours, rather than “pretty” (where it’s 2d 3h 15m 5s). I’m sure we’ll run it this way, but.. while we are testing your new version, I’ll temporarily set the time format to ‘pretty’ to make sure it works as you describe.

    Doug

    [cid:image001.png@01D170A2.58C4CB40]

  8. Xavier Arques

    Doug,

    I can't see the screenshot.Can you e-mail me at contact@seuqra.com Anyway, the regular expression will allow you to define the semantic of your Jira format. So you should be able to test with 2d 3h 15m 5s without switching to pretty format.

    My concern now is about the unit of the result. If your time format includes week, day, hour and second, then the result will be in seconds. As I mentioned before, "The result will be a number of hours (or seconds depending on the replacement string ) that cannot be transformed to the initial format".

    To be sure I understand your needs, can you send me a table with some lines containing Initial Estimate values and the expected Pivot Table sum (with the unit and the format)

    Xavier

  11. Doug Robinson reporter
    • changed status to open

    pivoting data in JIRA filters seems to work well but the column totals don't seem to always work, see highlighted values in the attached image. the column headings are "month/year" (calculated text custom field) of the Planned End date in our issues (so we can group the issues to reduce the size of the pivot table)

    pivot totals not consistent.PNG

  12. Xavier Arques

    Doug,

    Strange behavior. There should be an error during the regex substitution or the expression evaluation.

    Should it be possible to send me the HTML table content generated by the Jira macro so that I can reproduce the bug with a unit test ?

    And also the regex and replacement string you use

    Thanks

    Xavier

  13. Xavier Arques

    In your first example, 1030 is the sum of the previous Grand Total values ! But not in the second table.

    Could you add the Grand Total for rows to check if the last column's total changes ?

    Thanks

  14. Doug Robinson reporter

    Xavier, it looks like the table wants to always display the grand total, even if it has to override the the last column data to do it. how do I attached the excel file?

    pivot without totals.PNGpivot with totals.PNG

  16. Xavier Arques

    Doug,

    There is 2 bugs:

    1. The last column is overridden by the Grand Total
    2. The Pivot ignores empty values for columns and rows but uses it to calculate the Grand Total.

    Let me explain: In the Excel file you sent me, for value 201603, there is 3 Remaining Estimate values with an empty value for cost center/market/project. Those values are missing in the Pivot but if you sum those values: 152+16= 168 and you add 88, you obtain 256, the total displayed in the Pivot. So the Total is OK, but Pivot rows with empty cost center/market/project are missing.

    Until I fix those bugs, can you modify your source table and add a non empty value for every cell used in the Pivot ?

    Thanks

    Xavier

  19. Log in to comment
Assignee
Type
bug
Priority
major
Status
resolved
Votes
0
Watchers
2
Jira Software: the preferred issue tracker for Bitbucket. Join the team!