The OneStream Community is temporarily frozen until June 29th due to the ongoing maintenance. Please read the blog post here to learn more.
Forum Discussion
Solved
Month Over Month Variance in the Row
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?
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
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
4 Replies
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.
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
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
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