Prior year in row with dynamic columns

[jmorrison]

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

[MikeG]

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]

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]

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]

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.