Skip to main content
R
RyanDiehl
Newcomer
January 17, 2022
Solved

Month Over Month Variance in the Row

  • January 17, 2022
  • 3 replies
  • 0 views

I am building a cube view with two rows and 12 columns. Assume POV=2021M12:

 

The first row is A#Sales and in the second row I want the variance of the sales from row one this month minus the sales last month. (Think POV minus POVPrior1)

 

In the Column I have one Column with T#POV.AllPriorInYear.

 

The problem is that the formula in my month over month isn't smart enough to populate 12 cells of variances. 

 

I know I could do 12 separate columns and use CVC, but that isn't a great option to me for other reasons. Anyone have any ideas on how to do this?

Best answer by Sai_Maganti

Right in that case you might want to calculate your variance using an Account member (create new one) and use that member in the second row. I think that should work

3 replies

S
Sai_Maganti
Newcomer
January 18, 2022

I have done this by writing all the 12 variance formulas in one column as opposed to 12 columns like below:

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...not the best solution but I couldn't find any other way to do this.

R
RyanDiehlAuthor
Newcomer
January 18, 2022

Sai, thanks for the idea. The problem with the approach you listed is that the variance GetDataCell would apply to all of the rows. I need to have the first row display the Sales number for the period and the second row show the month over month variance. 

Appreciate the idea!

 

Ryan

S
Sai_MagantiAnswer
Newcomer
January 18, 2022

Right in that case you might want to calculate your variance using an Account member (create new one) and use that member in the second row. I think that should work

R
RyanDiehlAuthor
Newcomer
January 18, 2022

Thanks for the suggestion, I could probably put this in a UD8 and make it dynamic for all accounts but for the purposes of this thread, below is what I ended up using as the solution as a dynamic reporting account.

----------

'Variance to Prior Month:

'This account member is for reporting purposes.
'It displays the variance of the Cost of Sales relative to the same time period for the prior month.
Dim selectedTime As Member = api.Pov.Time
Dim viewMember As ViewMember = ViewMember.GetItem(api.Pov.View.MemberPk.MemberId)
If viewMember.IsAnnotationType Then
'Return an empty string if this is a text-based dataCell.
Return ""
Else
'Returns Cost of Sales for the current time period less the Cost of Sales for one month prior. Should only be used for V#MTD
Return api.Data.GetDataCell("T#[" + selectedTime.Name + "]:A#Cost_of_Sales - T#[Year(" + selectedTime.Name + ")PeriodPrior1(" + selectedTime.Name + ")]:A#Cost_of_Sales")

End If


Return Nothing

Terms & ConditionsAccessibility statement