01-17-2022 06:35 PM - last edited on 05-24-2023 11:47 AM by JackLacava
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?
Solved! Go to Solution.
01-18-2022 07:47 AM
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
01-18-2022 09:11 AM
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
01-18-2022 04:10 AM
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.
01-18-2022 07:27 AM
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
01-18-2022 07:47 AM
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
01-18-2022 09:11 AM
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