CubeView - Calculate the Variance for multiple columns ( Column-by-Column)

aneupane
New Contributor III

CubeView - Calculate the Variance for multiple columns ( Column-by-Column)

Hi there,

I am trying to calculate variance Column-by-Column for all time members of Current Year Vs. Prior year and looking for good option for performance optimization.

Please find below the details:

I have CubeView with 3 columns.

aneupane_0-1638384813684.png

 

Column 1: T#Year(|!SelectTimeYear!|).TreeDescendantsInclusiveR

This gives 19 Columns of current year (year, half year, Quarter and Months)

Column 2: T#YearPrior1(|!SelectTimeYear!|).TreeDescendantsInclusiveR

This gives 19 Columns of Prior year (year, half year, Quarter and Months)

Column 3: Need to generate 19 columns for variance between respective time members form previous members.

  1. 2020 vs 2019, 2020H1 vs 2019H1 etc.

following are some options I have implemented or have thought of.  I am trying to work Option 3 but don't know how to do it.

Option 1: I am able to generate the 19 variance columns using UD8 member but I believe this is impacting the performance of Cube view. 

T#Year(|!SelectTimeYear!|).TreeDescendantsInclusiveR + UD8 member => working fine but looking if there is other approach for better performance.

Option 2: Use something like GetDataCell(CVC(Col1, First) - CVC(Col1, 2)) formula. But this will need to manually create 19 different columns in Cubeview which is not ideal.

Option 3: This is what I am trying to achieve but don''t know how to do it.

Combine the Timemember with getDataCell using column.

T#Year(|!SelectTimeYear!|).TreeDescendantsInclusiveR + GetDataCell(CVC(Col1, First) - CVC(Col1, 2))

Any suggestion would be very helpful.

Thank you.

1 ACCEPTED SOLUTION

aneupane
New Contributor III

We have decided to go with UD8 member as that seems easiest from build and maintenance perspective. 

View solution in original post

3 REPLIES 3

Sai_Maganti
Contributor II

Another way to do this for your Option 3 would be as below in your third column

GetDataCell(Variance(T#|!SelectTimeYear!|M1,T#[YearPrior1(|!SelectTimeYear!|)]M1)):Name("Var M1"),
GetDataCell(Variance(T#|!SelectTimeYear!|M2,T#[YearPrior1(|!SelectTimeYear!|)]M2)):Name("Var M2")

and so on for all your 19 time members

So instead of creating 19 columns you could do with 19 formulas. I don't know of any other easy way to achieve this.

Best

Sai

aneupane
New Contributor III

Thank you Sai for good suggestion. 

aneupane
New Contributor III

We have decided to go with UD8 member as that seems easiest from build and maintenance perspective.