Forum Discussion

Qiniso's avatar
Qiniso
New Contributor III
10 months ago

Cube View - Average column based on fixed denominator of 4; numerator is column that have data

Hi, 

I am trying to create a column in cube view, and that column must average based on columns (out of the 4) that have data and ignore the column with 0 or no data. Denominator will be based on number of columns with data e.g. if columns 1 and 2 have data, then 2/4 will be the denominator. Numerator will be data that exist in those 2 columns. 

Any idea how I can achieve this as Onestream doesn't support such requirement on prebuilt settings. 

 

Reports
  • JackLacava's avatar
    JackLacava
    Honored Contributor
    10 months ago

    I can see two options, but the main strategy is really the same: don't reason in terms of columns, but in terms of intersections. You can easily have code that checks those 4 intersections, and calculates what you need.

    The practicalities boil down to a choice between a Dynamic Calc formula, placed on something like a UD8 member; and a custom GetDataCell function. The main difference is that the former can be surfaced in various ways, it's a bit more like simple cube data; whereas the latter is strictly limited to Cube Views.