Total DateDiff in higher layer

Questions & Answers
1

Hi there,

I’m currently trying to test, if easyBI is maybe a valuable tool for us. What I’m trying in detail at the moment is, to calculate the difference of two custom date fields, what in general is working fine, but I’m for some reason not able to see the total sum for the higher instance.

Example:
Incident 1 -> Diff = 3h 30min
Incident 2 -> Diff = 1h 30min
Inicdent 3 -> Diff = 1h 00min

Each Incident is realted to a different Software HUB for example, so If I choose HUB as a Row in the report, I assumed, that I will see the total sum of all Incident, which belongs to this HUB. But this isn’t the case. I can see my results only, as long as I show all single Incidents.

I hope this is explained good enough, so that somebody can help me out :slight_smile:

As you can see in the screenshot (removed sensitive part), wach line with a Duration is a single Incident, but in the higher layers (INCIDENT MGMT and All Issues) I can’t see the total time.

image
image1109×602 21.8 KB

Many thanks in advance,
Thomas

2

Hi!

From the context, I imagine that you are using issue properties that are visible only on the individual issue level. To sum the value over a set of issues, you would need to wrap this in a Sum(…) function.

Try the following (make sure to enter the correct custom date field names and the formula used before, see comments)

Sum(
  Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]), --filter set of issues
    NOT IsEmpty([Issue].CurrentMember.get('Date 1')) AND -- that have first date
    NOT IsEmpty([Issue].CurrentMember.get('Date 2')) AND -- and last date
    [Measures].[Issues with Date 1] > 0), -- greater than zero filter for all other report context
    -- HERE please enter the formula used on issue level
)

Lauma / support@eazybi.com