Forum Discussion

JuleneDS's avatar
JuleneDS
New Contributor II
8 days ago

Sum of accounts at base levels

I would like to return a sum of all rows in my cube view but using the 'sum of rows' is only returning the first result for every account under the main hierarchies I'm calling. 

This is the literal statement I would like to sum -

GetDataCell(A#BALANCE_SHEET.Base.Remove(A#32000).Where(AccountType DoesNotContain 'Liability') - A#BALANCE_SHEET.Base.Remove(A#32000).Where(AccountType = 'Liability') - A#32000:F#BegBal - A#INCOME_STATEMENT.Base.Where(AccountType = 'Revenue') + A#INCOME_STATEMENT.Base.Where(AccountType DoesNotContain 'Revenue'))

Has anyone done something similar through an XFBR string? 

Cube View
  • BeatlesForever's avatar
    BeatlesForever
    New Contributor III
    8 days ago

    Hello,

    If it's a KPI that users use frequently, I would create a KPI account with the Account Type and Formula Type set to "DynamicCalc". I would also try to use existing accounts for the calculation.

    In the Formula field, you could write something like the following:

    Return api.Data.GetDataCell("A#TotalAssets:F#CLO - A#TotalLiabilities:F#CLO - A#32000:F#OPE - A#TotalRevenue:F#None + A#TotalExpenses:F#None")

    Hope it helps!

  • T_Kress's avatar
    T_Kress
    Valued Contributor
    5 days ago

    Could you not just build an alternate account rollup in the account dimension to achieve what you are trying to achieve through a bunch of cube view math?  The account dimension auto-aggregates so adding an alternate account rollup does not hurt consolidation performance or cube view performance.  And it may be far easier than trying to do a GetDataCell with complex math directly on the cube view.