Sum values in hierarchy

Questions & Answers
#1

Hi!
I have “Start date” and “End date” in issues
When I get all issues in that date range in table, results don’t sum in hierarchy levels
I use these formula
Count(
Filter(Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
DateCompare(
[Measures].[Issue Vacation End Date],
[Time].CurrentHierarchyMember.StartDate
)>=0
AND
DateCompare(
[Measures].[Issue Vacation Start Date],
[Time].CurrentHierarchyMember.StartDate
)<=0
)
)

And I get result like that (you can see that Jan 2020 is “0”)

vac
vac948×1158 84.1 KB

What I need to do to sum values in hierarchy (so I get “21” in Jan 2020)?

Cheers

#2

Hi @Stas

You get 0 result as the method “.startdate” for “Time” dimension member for the month, quarter and year level would return the first date and such calculation of yours would actually return the result of 1st day of month to display it on month, quarter, year results and it would indeed not work on the hierarchy.
You would need to extend the logic - what this calculation should do for each Time hierarchy level

Try these steps:

  1. Make sure that custom fields “Vacation Start date” and “Vacation end date” is selected in import options page to be imported both as properties and measures.

  2. Then extend your calculated measure with additional logic.

    CASE WHEN
    [Time].CurrentHierarchyMember.Level.name = “Day”
    THEN
    Count(
    Filter(Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
    DateCompare(
    [Measures].[Issue Vacation End Date],
    [Time].CurrentHierarchyMember.StartDate
    )>=0
    AND
    DateCompare(
    [Measures].[Issue Vacation Start Date],
    [Time].CurrentHierarchyMember.StartDate
    )<=0
    )
    )
    ELSE
    Count(
    Filter(Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
    [Measures].[Issues with vacation end date] >0 --when imported as measures
    OR
    [Measures].[Issues with vacation start date] >0 --when imported as measures
    )
    )
    END

It would include all issues to higher levels if one of the dates belong to the month, quarter or year.

Martins / eazyBI support

#3

Thanks!!!
But It does count and not sum(
I need to count vacation days total, not just issues in range of start/end day

SharedScreenshot
SharedScreenshot333×1157 74.5 KB

As you can see its 21 day in Jan total, but formula shows only 2. Thats true cuz I have 2 issues with vacations (8-22 jan and 15-20 jan), but I need to count vacation days.

#4

Hi,

In that case, I would recommend creating a separate calculated measure (that would point to the first measure and return the results on higher levels) using this coding approach.

CASE WHEN
[Time].CurrentHierarchyMember.Level.name = "Day"
THEN
Val([Measures].[your first calculated measure]) --update here
ELSE
Val(sum(
Filter(Descendants([Time].CurrentHierarchyMember, [Time].[Day]),
[Measures].[your first calculated measure]>0 --update here
),
[Measures].[your first calculated measure] --update here
))
END

Note 3 places in the formula where you must use your calculated measure instead.
The new code would work on for a month, quarter, year, and default level in Time dimensions, however the original one only for the day level.

Martins / eazyBI support

#5

OMG it works Thanks!!! <3

#6

found a bug
It counts all issues and not filtering by members(

image
image1004×835 261 KB

There is 1 issue on 1 assignee in 1 team, but it shows like both assignees have vacations

#7

Hi,

In this case, it is the context of your first calculated measure that does not re-calculate results when you add more dimensions (like assignee or team).

Try changing your first calcualted measures as follows:

CASE WHEN
[Time].CurrentHierarchyMember.Level.name = "Day"
THEN
Count(
Filter(Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
DateCompare(
[Measures].[Issue Vacation End Date],
[Time].CurrentHierarchyMember.StartDate
)>=0
AND
DateCompare(
[Measures].[Issue Vacation Start Date],
[Time].CurrentHierarchyMember.StartDate
)<=0
AND
(
[Measures].[Issues created],
[Time].Currenthierarchy.DefaultMember
)>0
)
)
ELSE
Count(
Filter(Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
[Measures].[Issues with vacation end date] >0 --when imported as measures
OR
[Measures].[Issues with vacation start date] >0 --when imported as measures
)
)
END

Martins / eazyBI support

#8

That works! Thank you!