12-01-2021 01:54 PM
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.
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.
Solved! Go to Solution.
12-06-2021 04:18 AM
We have decided to go with UD8 member as that seems easiest from build and maintenance perspective.
12-02-2021 10:29 AM
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
12-02-2021 12:18 PM
Thank you Sai for good suggestion.
12-06-2021 04:18 AM
We have decided to go with UD8 member as that seems easiest from build and maintenance perspective.