Skip to main content
J
OneStream Employee
jmorrisonOneStream Employee
OneStream Employee
June 8, 2023

Prior year in row with dynamic columns

  • June 8, 2023
  • 3 replies
  • 0 views

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        

    3 replies

    MikeG
    MikeG
    Contributor
    June 8, 2023

    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.

    MikeG_0-1686246745262.png

     

    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
     
    MikeG_1-1686246833919.png

     

     

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

     

    Good luck,

    Mike G

    Archetype Consulting

     

      J
      OneStream Employee
      jmorrisonOneStream EmployeeAuthor
      OneStream Employee
      June 12, 2023

      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
        MikeG
        Contributor
        June 12, 2023

        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 $)

        MikeG_0-1686588025441.png

         

         

        D
        DiegoRomero
        Newcomer
        October 30, 2023

        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. 

        Terms & ConditionsAccessibility statement