Skip to main content
Newcomer
Jun 6, 2022 6:58 AM UTC

GetCellvalue to sum using substitution variables

Team

Is there a way where i can sum the columns/data using Variables. 

Example: Instead of using GetDataCell(T#2021M1+T#2021M2):Name(Total) , can i use some kind of variable which calculates based on POV/Cube view

GetDataCell(T#Quarters):Name(Total)

 

5 replies

Veteran
Jun 6, 2022 7:47 AM UTC

Hello,

Please explore using CVC on your cube view columns:

GetDataCell(CVC(SomeColumnName) + CVC(SomeOtherColumnName)):Name(Header Name)

 

GetDataCell(CVC(Col1) - CVC(Col2)):Name(Variance)
Examples of Column Math:
GetDataCell(CVC(Col1) + 1):Name(Column Plus One)
GetDataCell(CVC(Col1) * (-1)):Name(Column with Sign Flipped)

Hope this helps.

Thanks

SKVKAuthor
Newcomer
Jun 6, 2022 8:23 AM UTC

Thank you. It works fine only when you the columns explicitly have it the Cube Views.

In the first column which i have named as "Qtrs" have a time formula as "T#2021.Quarters" which in Data explorer expands and gives me all the quarters of 2021 (Q1, Q2, Q3, Q4). 

If i use your suggested formula in a new column, it gives only the data of Q1

Formula used - GetDataCell(CVC(T#2021.Quarters) + 1):Name(Sum)

 

 

Veteran
Jun 6, 2022 10:26 AM UTC

Please use column name in the CVC

GetDataCell(CVC(Qtrs) + 1):Name(Sum)

Thanks

SKVKAuthor
Newcomer
Jun 6, 2022 1:15 PM UTC

I did try placing the column name between the CVC( ) - Screenshot attached. But again it gives me only the result of first quarter, rather than adding 4 quarters

Qtrs.JPG

 

Veteran
Jun 13, 2022 12:40 PM UTC

Hi, 

Could this work? POV could also be CV depending on which year you want to substitute.

GetDataCell(T#|PovYear|Q1+T#|PovYear|Q2+T#|PovYear|Q3+T#|PovYear|Q4):Name(Total)