Frequency of failed date comparison over time

Questions & Answers
1

I’ve written a calculated field in Measures that shows [-1, 0, 1] depending on the result of comparing two dates [failed, not necessary, succeeded].

When I put this in a report, it shows OK against each individual task, but doesn’t roll up to the other levels - e.g. issue type, project, or more specifically over time.

The field is:

CASE WHEN not isempty([Measures].[Issue Flag Date/Time])THEN
CASE WHEN isempty([Measures].[Issue Resolution Date]) THEN
CASE WHEN DateDiffDays(Now(), [Measures].[Issue Flag Date/Time]) < -1 THEN 1 END
ELSE
CASE WHEN DateDiffDays([Measures].[Issue Resolution Date], [Measures].[Issue Flag Date/Time]) < -1 THEN 1 END
END
ELSE
0 /* No Flag Date set */
END

Any idea what I’m doing wrong? Might be obvious, but still pretty new to EazyBi

2

Steve,

You are not doing anything wrong. The issue here is that there are no dates on these higher levels that can be compared. The Flag date, Resolution date are properties of each individual issue and there is no one Flag or Release date that can be displayed on Project or other aggregation levels.

What would be the result that you would expect on the Project or Time levels?

3

Lauma,

What I’m looking for is a chart over time of the total number of issues that have this flag. So, in a particular week if 10 issues have “1” against this calculated field, it should chart a 10 on a line graph.

I think I just don’t quite understand how the cube works yet!

4

For this you would use the Sum() function over set of issues. In following example the issues will be grouped on Time dimension based on the Flag date.

Sum(Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
  not isempty([Issue].CurrentMember.get('Flag Date/Time')) AND
  [Measures].[Issues with flag date/time] > 0 ),
CASE WHEN isempty([Issue].CurrentMember.get('Resolved at')) THEN
  CASE WHEN DateDiffDays(Now(), 
              [Issue].CurrentMember.get('Flag Date/Time')) < -1 
       THEN 1 END
  ELSE
  CASE WHEN DateDiffDays([Issue].CurrentMember.get('Resolved at'),
              [Issue].CurrentMember.get('Flag Date/Time')) < -1 
       THEN 1 END
END
)

Please note that for large accounts this calculation might be slow and it might be necessary to pre-calculate the value using JavaScript in advanced settings. If you need assistance with this, please contact support@eazybi.com.

5

I don’t quite get what the "[Measures].[Issues with flag date/time] " bit is?