Data Reliability Problem

Questions & Answers
#1

Hello Everyone.

In the picture below, there are two issues.

  1. Calculating the Count on which issues were generated up to a certain date, there is a difference between the number of Sep 2019 cases and the actual total number

  2. Closed issues show data up to 10/10, but there is a difference in the September data.
    (“issues closed count” & “2019-10-10_Closed Count”)

The Measure I wrote is as follows.

  • 2019-09-26_Created Count
    SUM(Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    DateBetween([Time].CurrentMember.StartDate,‘2017-01-01’,‘2019-09-26’)
    AND [Measures].[Issues created] > 0
    ))

  • 2019-10-10_Closed Count
    COUNT(Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    DateBetween([Time].CurrentMember.StartDate,‘2017-01-01’,‘2019-10-10’)
    AND [Measures].[Issues closed] > 0
    ))

image
image.png592×734 12.8 KB

We using Version 5.1.0.

Help me…

#2

The formula works correctly for Time members expanded to Day level. You are addressing a start date of Time members. For September 2019, this date is 01 SEP 2019. It matches the criteria between 2017-01-01 and 2019-09-26. Therefore, data for the whole of September is counted in, leading to a higher number of issues.

You can use a different approach and check if issue creation date (not a selected period on Rows) is between those two dates:

SUM(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    DateBetween([Issue].CurrentHierarchyMember.Get("Created at"),"2017-01-01","2019-09-26")
    AND
    DateInPeriod([Issue].CurrentHierarchyMember.Get("Created at"),
    [Time].CurrentHierarchyMember)
  ),
  [Measures].[Issues created]
)

You can use the same approach using Resolution date instead of Issue creation date for another formula.

The Total calculation sums up values from Rows (calculations for each day, ignoring results of September) and therefore in this context it gives correct results for you.

Daina / support@eazybi.com