Measure in aggregated level Half year is not displayed

Questions & Answers
1

Hi!!
we have created this report to display the Work In progress at Half levels and per months

image
image1355×719 67.1 KB

Where the definition of Halves is:

Aggregate(
  Iif (
    DateBetween(
      Now(),
      [Time].[Year].CurrentDateMember.StartDate,
      DateParse('Jul 1 ' || [Time].[Year].CurrentDateMember.Name)
    ),

    {
    [Time].[Previous Year H2],
    [Time].[Current Year H1]
    }
  ,
    {
    [Time].[Current Year H1],
    [Time].[Current Year H2]
    }
  )
)

And Current H1 (or H2) is:

Aggregate({
  [Time].[Quarter].DateMembersBetween('first day of the year', 'last day of June')
})

The Weekday in the filter is just:
[Time].CurrentHierarchyMember.Get(‘Week day name’)

And the WIP formula is basically the Average of one default formula:

IIf([Time].CurrentMember.Level.Name <> 'Day',
avg([Time].CurrentMember.Children,[Measures].[Issues history]),
[Measures].[Issues history])

So, why the value for the Half level is not displayed?

2

Hi @Nacho,

The “half” calculated members don’t return values because you aggregate aggregated calculated members, and the members you try to expand are already expanded - quarters, months.

I would recommend defining a new calculated hierarchy in the Time dimension to split the year in half. The “Half” part would be in the place of the “Quarter” level. Please see more details here - Custom time hierarchies.

The custom JavaScript code for the calculated hierarchy in the Time dimension could look similar to the one below:

const monthName = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"];

var month = timeValue.getMonth();
var year = timeValue.getFullYear();
const quarter = Math.ceil((month+1)/6);

return {
  year: year,
  year_name: year,
  quarter: quarter,
  quarter_name: "H" + quarter + " " + year,
  month: month + 1,
  month_name: monthName[month] + " " + year,
  day: timeValue.getDate(),
  day_name: strftime("%B %d %Y", timeValue)
}

See the Time dimension “Half” hierarchy configuration and view in the report below:

SCR-20240923-njvz
SCR-20240923-njvz2418×1386 407 KB

What is the purpose of the average of “Issues history”? I recommend replacing the [Time].CurrentMember.Children expression with ChildrenSet([Time].CurrentHierarchyMember).

Best,
Roberts // support@eazybi.com

3

mmmmm that’s why we are not displaying the Average per Half.

The idea of custom Hierarchy, I though to use it. However, if we implement in a Template account, it can’t be used because it has to be configured in every eazyBI account.

The reason behind that. It is to have the average per month of all of the issues in the aggregation of the different statuses that are not completed (Not Open neither Completed).
Edited: that’s the reason why we have “Issues History” with the Transition Status combined to filter those status per day/month

Is there any other better way to calculate this Average per month?

I will change that in the current formula in the meantime. Thanks for it