Same measure return different results in different charts

[Leon]

Dear There,

I got some calculation error need help urgently… Thanks!

. Our project measure issue between “Submitted” and “Resolved” status
. Our project use customer field “Need Engineer Support”, for each “Submitted” issue, its “Need Engineer Support” should be “Yes”
. Our project measure above issue data per month e.g. by end of each previous month and by latest time for existing month

Check screen shot below, the 1st highlight is showing 82 as latest submitted unresolved issue number by April, while the 2nd chart is showing 85, and the 3rd chart is showing 83 — the 85 should be correct number.

Could you help figure out what’s wrong in the calculation? Thanks!

The calculation in 1st chart
*CASE WHEN [Issue].CurrentMember.Level.Name <> ‘Issue’ *
AND [Measures].[Issues created]>0

*THEN*
*  Cache(*
*    NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),*
*      Cache([Measures].[Issues created]*
*          - [Measures].[Issues resolved])*
*    ))*
*    + [Measures].[Issues created]*
*    - [Measures].[Issues resolved]*
*  )*
*WHEN [Time].CurrentHierarchyMember IS [Time].CurrentHierarchy.DefaultMember*
*THEN NonZero([Measures].[Issues due])*
*ELSE*
*  -- optimized formula for drill through Issue*
*  NonZero(IIF(*
*      DateBeforePeriodEnd(*
*        [Issue].CurrentMember.get('Created at'),*
*        [Time].CurrentHierarchyMember) AND*
*      NOT DateBeforePeriodEnd(*
*        [Issue].CurrentMember.get('Resolved at'),*
*        [Time].CurrentHierarchyMember),*
*    ([Time].CurrentHierarchy.DefaultMember,*
*      [Measures].[Issues created]),*
*    0*
*  ))*
*END*

The calculation in 2nd chart – this shows the correct number

Row by Status

The calculation in 3rd chart – this is not showing the latest data of any month…

CASE WHEN [Issue].CurrentMember.Level.Name <> 'Issue'

AND [Measures].[Issues created]>0

THEN
  Cache(
    NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),
      Cache([Measures].[Issues submitted]
          - [Measures].[Issues resolved])
    ))
    + [Measures].[Issues submitted]
    - [Measures].[Issues resolved]
  )
WHEN [Time].CurrentHierarchyMember IS [Time].CurrentHierarchy.DefaultMember
THEN NonZero([Measures].[Issues due])
ELSE
  -- optimized formula for drill through Issue
  NonZero(IIF(
      DateBeforePeriodEnd(
        [Issue].CurrentMember.get('submitted at'),
        [Time].CurrentHierarchyMember) AND
      NOT DateBeforePeriodEnd(
        [Issue].CurrentMember.get('Resolved at'),
        [Time].CurrentHierarchyMember),
    ([Time].CurrentHierarchy.DefaultMember,
      [Measures].[Issues submitted]),
    0
  ))
END

[Leon]

export of report definition #1

{
“cube_name”: “Issues”,
“cube_reports”: [ {
“name”: “Open Defect Trend Metric”,
“folder_name”: “Leon Kan Li (please do not edit)”,
“result_view”: “line_chart”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Measures”,“selected_set”:["[Measures].[Total open active Issues]","[Measures].[Total created unresolved Leon]"],“members”:[]},{“name”:“Found By”,“selected_set”:["[Found By].[Engineering]","[Found By].[Customers]"],“members”:[],“bookmarked_members”:[]},{“name”:“Need Engineering Support”,“selected_set”:["[Need Engineering Support].[Need Engineering Support].Members"],“members”:[],“bookmarked_members”:[]}]},“rows”:{“dimensions”:[{“name”:“Time”,“selected_set”:["[Time].[Month].Members"],“members”:[],“bookmarked_members”:[]}]},“pages”:{“dimensions”:[{“name”:“Project”,“selected_set”:["[Project].[All Projects]"],“members”:[{“depth”:0,“name”:“All Projects”,“full_name”:"[Project].[All Projects]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false},{“depth”:1,“name”:“ThinOS Defect Management”,“full_name”:"[Project].[ThinOS Defect Management]",“drillable”:true,“key”:“THINOS”,“parent_full_name”:"[Project].[All Projects]"}],“bookmarked_members”:[],“current_page_members”:["[Project].[ThinOS Defect Management]"]},{“name”:“Sub Project”,“selected_set”:["[Sub Project].[All Sub Projects]"],“members”:[{“depth”:0,“name”:“All Sub Projects”,“full_name”:"[Sub Project].[All Sub Projects]",“drillable”:true,“type”:“all”}],“bookmarked_members”:[],“current_page_members”:["[Sub Project].[All Sub Projects]"]}]},“options”:{“nonempty”:true},“view”:{“current”:“line_chart”,“maximized”:false,“line_chart”:{“area”:false,“swap_axes”:false,“data_labels”:true,“series_options”:{}},“table”:{“freeze_header”:false}}}
} ],
“calculated_members”: [{“name”:“Engineering”,“dimension”:“Found By”,“formula”:"[Found By].[Engineering - Automation] + [Found By].[Engineering - SQA BVT] + [Found By].[Engineering - SQA PASS]\n+ [Found By].[Engineering - DEV] + [Found By].[Engineering - Pre SQA] + [Found By].[Engineering - SQA post release]",“format_string”:""},{“name”:“Customers”,“dimension”:“Found By”,“formula”:"[Found By].[Customer]",“format_string”:""},{“name”:“Total open active Issues”,“dimension”:“Measures”,“formula”:“CASE WHEN [Issue].CurrentMember.Level.Name <> ‘Issue’ \n\nAND [Measures].[Issues created]>0\n\nTHEN\n Cache(\n NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),\n Cache([Measures].[Issues created]\n - [Measures].[Issues closed])\n ))\n + [Measures].[Issues created]\n - [Measures].[Issues closed]\n )\nWHEN [Time].CurrentHierarchyMember IS [Time].CurrentHierarchy.DefaultMember\nTHEN NonZero([Measures].[Issues due])\nELSE\n – optimized formula for drill through Issue\n NonZero(IIF(\n DateBeforePeriodEnd(\n [Issue].CurrentMember.get(‘Created at’),\n [Time].CurrentHierarchyMember) AND\n NOT DateBeforePeriodEnd(\n [Issue].CurrentMember.get(‘Closed at’),\n [Time].CurrentHierarchyMember),\n ([Time].CurrentHierarchy.DefaultMember,\n [Measures].[Issues created]),\n 0\n ))\nEND”,“format_string”:""},{“name”:“Total created unresolved Leon”,“dimension”:“Measures”,“formula”:“CASE WHEN [Issue].CurrentMember.Level.Name <> ‘Issue’ \nAND [Measures].[Issues created]>0\n\nTHEN\n Cache(\n NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),\n Cache([Measures].[Issues created]\n - [Measures].[Issues resolved])\n ))\n + [Measures].[Issues created]\n - [Measures].[Issues resolved]\n )\nWHEN [Time].CurrentHierarchyMember IS [Time].CurrentHierarchy.DefaultMember\nTHEN NonZero([Measures].[Issues due])\nELSE\n – optimized formula for drill through Issue\n NonZero(IIF(\n DateBeforePeriodEnd(\n [Issue].CurrentMember.get(‘Created at’),\n [Time].CurrentHierarchyMember) AND\n NOT DateBeforePeriodEnd(\n [Issue].CurrentMember.get(‘Resolved at’),\n [Time].CurrentHierarchyMember),\n ([Time].CurrentHierarchy.DefaultMember,\n [Measures].[Issues created]),\n 0\n ))\nEND”,“format_string”:""}]
}

[Leon]

report #3 definition export

{
“cube_name”: “Issues”,
“cube_reports”: [ {
“name”: “ThinOS SUS Monthly Metric #1 (under development)”,
“folder_name”: “Leon Kan Li (please do not edit)”,
“result_view”: “table”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Measures”,“selected_set”:["[Measures].[Issues created]","[Measures].[Issues submitted]","[Measures].[Total submitted unresolved]","[Measures].[Total unsubmitted]","[Measures].[Total created unresolved Leon]"],“members”:[]}]},“rows”:{“dimensions”:[{“name”:“Time”,“selected_set”:["[Time].[Month].Members"],“members”:[],“bookmarked_members”:[]}]},“pages”:{“dimensions”:[{“name”:“Project”,“selected_set”:["[Project].[All Projects]"],“members”:[{“depth”:0,“name”:“All Projects”,“full_name”:"[Project].[All Projects]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false},{“depth”:1,“name”:“ThinOS Defect Management”,“full_name”:"[Project].[ThinOS Defect Management]",“drillable”:true,“key”:“THINOS”,“parent_full_name”:"[Project].[All Projects]"}],“bookmarked_members”:[],“current_page_members”:["[Project].[ThinOS Defect Management]"]},{“name”:“Sub Project”,“selected_set”:["[Sub Project].[All Sub Projects]"],“members”:[{“depth”:0,“name”:“All Sub Projects”,“full_name”:"[Sub Project].[All Sub Projects]",“drillable”:true,“type”:“all”}],“bookmarked_members”:[],“current_page_members”:["[Sub Project].[All Sub Projects]"]},{“name”:“Found By”,“selected_set”:["[Found By].[All Found Bies]"],“members”:[{“depth”:0,“name”:“All Found Bies”,“full_name”:"[Found By].[All Found Bies]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false},{“depth”:1,“name”:“Customer”,“full_name”:"[Found By].[Customer]",“parent_full_name”:"[Found By].[All Found Bies]"}],“bookmarked_members”:[],“current_page_members”:["[Found By].[Customer]"]}]},“options”:{“nonempty”:true},“view”:{“current”:“table”,“maximized”:false,“table”:{}}}
} ],
“calculated_members”: [{“name”:“Issues submitted”,“dimension”:“Measures”,“formula”:"([Measures].[Transitions to status issues count],\n[Transition Status].[Submitted]\n \n)",“format_string”:""},{“name”:“Total submitted unresolved”,“dimension”:“Measures”,“formula”:“CASE WHEN [Issue].CurrentMember.Level.Name <> ‘Issue’\n\nAND [Measures].[Issues created]>0\n\nTHEN\n Cache(\n NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),\n Cache([Measures].[Issues submitted]\n - [Measures].[Issues resolved])\n ))\n + [Measures].[Issues submitted]\n - [Measures].[Issues resolved]\n )\nWHEN [Time].CurrentHierarchyMember IS [Time].CurrentHierarchy.DefaultMember\nTHEN NonZero([Measures].[Issues due])\nELSE\n – optimized formula for drill through Issue\n NonZero(IIF(\n DateBeforePeriodEnd(\n [Issue].CurrentMember.get(‘submitted at’),\n [Time].CurrentHierarchyMember) AND\n NOT DateBeforePeriodEnd(\n [Issue].CurrentMember.get(‘Resolved at’),\n [Time].CurrentHierarchyMember),\n ([Time].CurrentHierarchy.DefaultMember,\n [Measures].[Issues submitted]),\n 0\n ))\nEND”,“format_string”:""},{“name”:“Total unsubmitted”,“dimension”:“Measures”,“formula”:“CASE WHEN [Issue].CurrentMember.Level.Name <> ‘Issue’ \nAND [Measures].[Issues created]>0\n\nTHEN\n Cache(\n NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),\n Cache([Measures].[Issues created]\n - [Measures].[Issues submitted])\n ))\n + [Measures].[Issues created]\n - [Measures].[Issues submitted]\n )\nWHEN [Time].CurrentHierarchyMember IS [Time].CurrentHierarchy.DefaultMember\nTHEN NonZero([Measures].[Issues due])\nELSE\n – optimized formula for drill through Issue\n NonZero(IIF(\n DateBeforePeriodEnd(\n [Issue].CurrentMember.get(‘Created at’),\n [Time].CurrentHierarchyMember) AND\n NOT DateBeforePeriodEnd(\n [Issue].CurrentMember.get(‘submitted at’),\n [Time].CurrentHierarchyMember),\n ([Time].CurrentHierarchy.DefaultMember,\n [Measures].[Issues created]),\n 0\n ))\nEND”,“format_string”:""},{“name”:“Total created unresolved Leon”,“dimension”:“Measures”,“formula”:“CASE WHEN [Issue].CurrentMember.Level.Name <> ‘Issue’ \nAND [Measures].[Issues created]>0\n\nTHEN\n Cache(\n NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),\n Cache([Measures].[Issues created]\n - [Measures].[Issues resolved])\n ))\n + [Measures].[Issues created]\n - [Measures].[Issues resolved]\n )\nWHEN [Time].CurrentHierarchyMember IS [Time].CurrentHierarchy.DefaultMember\nTHEN NonZero([Measures].[Issues due])\nELSE\n – optimized formula for drill through Issue\n NonZero(IIF(\n DateBeforePeriodEnd(\n [Issue].CurrentMember.get(‘Created at’),\n [Time].CurrentHierarchyMember) AND\n NOT DateBeforePeriodEnd(\n [Issue].CurrentMember.get(‘Resolved at’),\n [Time].CurrentHierarchyMember),\n ([Time].CurrentHierarchy.DefaultMember,\n [Measures].[Issues created]),\n 0\n ))\nEND”,“format_string”:""}]
}

[daina.tupule]

There are different calculations and context in all reports. You are using our default measure Open issues for the first chart with some additional filters by dimensions.

For the second calculation, you are using measure Issues submitted instead of Issues created. Measure Issues submitted is based on issue change history. There could be some cases when resolved issues were not submitted. And this could lead to unpredicted results. You are using a distinct count measure. However, it still can give you double counting with PreviousPeriods function.

I would suggest using a different approach for counting how many issues in certain statuses you have. Please use measure Issues history with dimension Transition status in the report. You can create a calculated member in Transition status dimension similar to the one you created in Status dimension to represent all transition statuses representing submitted open issues over time. Use this calculated member together with measure Issues history to see open submitted issues over time.

The Cumulative Flow diagram report in our demo account is very similar to this:
https://eazybi.com/accounts/1000/cubes/Issues/reports/187032-cumulative-flow-diagram

Daina / support@eazybi.com