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

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.

 

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.