Hierarchy Measure Methodology in QQube | CLEARIFY

Hierarchy Measure Category

These start with [DAXP and are hidden. Their purpose is to perform functions to handle ragged hierarchies in financial statement groupings and account levels.

For instance if you drill down on a level 1 account, and some of those have sub-accounts at level 2 or greater, you won't get blank rows where there are NOT level 2 or greater sub-accounts. 

These negate the need for a mechanism such as used in our Excel Add-In - the Presentation Cleanup.

Where Used

They are used in the following Data Models:

• Jobs, Time and Mileage
  • Profit and Loss by Job (Flexible Period)
  • Profit and Loss by Job by Class (Flexible Period)
• Financial Statements
  • Balance Sheet (Standard Monthly Period)
  • Balance Sheet (Flexible Period)
  • Profit and Loss (Standard Monthly Period)
  • Profit and Loss (Flexible Period)
• Financial Statements by Class
  • Balance Sheet (Standard Monthly Period)
  • Profit and Loss (Standard Monthly Period)
  • Profit and Loss (Flexible Period)
• 52/53 Periods Profit and Loss
• 52/53 Periods Profit and Loss by Class
• Trial Balance - Current
• Trial Balance Activity
• Trial Balance Activity by Class
• Profit and Loss Detail

There are two formulas:

• [DAXP] AccountRowDepth  Finds the maximum level.
• [DAXP] AccountBrowseDepth  Nested IF statements to determine what level is being activated in the visualization or analytic.

Example

Both of these measure are used in this ".Hierarchy Actual" Formula in the Flexible Period Profit and Loss Data Model

.Hierarchy Actual =
VAR ProfitAndLossActual =
    SUM ( 'PROFIT AND LOSS MEASURES'[Actual Amount] )
        * MAX ( 'Account'[Sign To Apply] )
RETURN
    VAR AccountBrowseDepth = [[DAXP]] AccountBrowseDepth]
    VAR AccountRowDepth = [[DAXP]] AccountRowDepth]
    VAR _IsLeaf =
        IF (
            HASONEVALUE ( Account[Account Has Children] ),
            VALUES ( Account[Account Has Children] )
        )
    VAR Result =
        SWITCH (
            TRUE (),
            AccountBrowseDepth = AccountRowDepth + 1
                && NOT ( ISBLANK ( ProfitAndLossActual ) )
                    && _IsLeaf = TRUE (), ProfitAndLossActual,
            AccountBrowseDepth <= AccountRowDepth, ProfitAndLossActual
        )
    RETURN
        Result