Forum Discussion

jmorrison's avatar
jmorrison
New Contributor
2 years ago

Prior year in row with dynamic columns

Hello I am trying to create the below CV. I am struggling with the Account % Change from PY. 

Is there a way to reference the column time - 1 year and still have it dynamic?

 

  2022m1 2022m2 2022m3 2022Q1
Account        
Account % Change from PY        
         
         
  T#|!ParamQtr!|.base T#|!ParamQtr!|    
Account        
Account % Change from PY        
Cube View
Parameters
  • MikeG's avatar
    MikeG
    Contributor III
    2 years ago

    You could do a Row/Col reference in your Cube View.  Or, you could create a dynamic UD8 member and drop that in your Cube View.  As a best practice I'd recommend the UD8 route as those can be re-usable across numerous Cube Views.

     

    The Dynamic Calc member formula then returns the variance.  Included here is a check to make sure this does not run against Annotation type data cell intersections.

     

    'This UD member is for reporting purposes.
    'It displays the variance of the selected account's amount relative to the prior year.
     
    'Only run if the view is not of an annotation type
    If (Not ViewMember.IsAnnotationTypeViewId(api.Pov.View.MemberId)) Then
    Return api.Data.GetDataCell("U8#None - T#POVPrior12:U8#None")
    End If
     
    Return Nothing
     

     

     

    Your application specific POV may need to be updated, but this should get you 90% there.

     

    Good luck,

    Mike G

    Archetype Consulting

     

    • jmorrison's avatar
      jmorrison
      New Contributor
      2 years ago

      Thanks Mike. The problem I am having is that POV prior 12 does not work with the .base in column 1. It is giving me the same PY T#  for all three months in the quarter. For example if 2022 Q1 was selected It would give me 2021M1 for months 1-3. 

  • MikeG's avatar
    MikeG
    Contributor III
    2 years ago

    Do you have the Prior Period (Prior Year) as a Column in your Cube View?  if the data is present in the Column you could do CVC math.  Send a screen shot of your editor and I could help with the specific syntax in your use case.

     

    Something similar to this:  These are Columns in a particular Cube View and how the CVC dynamic math syntax would look:

    S#Actual:GetDataCell(CVC(Actual_YTD) - CVC(CompScen_YTD)):Name(Var $)

     

     

  • DiegoRomero's avatar
    DiegoRomero
    New Contributor II
    2 years ago

    Hi!

    I have a similar problem but with Rows, I am trying to do the following but the time filter doesn't work:

    GetDataCell(Variance(CVR(Row8),CVR(Row8):T#PovPrior1)):Name(Incremental RWA EOP)

    Row 8 is also a CVR calculated row. I have Quarters in columns, so need to do the formula as dynamic as possible.