Forum Discussion

RyanDiehl's avatar
RyanDiehl
New Contributor III
3 years ago

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?

Cube View
  • Sai_Maganti's avatar
    Sai_Maganti
    3 years ago

    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

  • RyanDiehl's avatar
    RyanDiehl
    3 years ago

    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

  • Sai_Maganti's avatar
    Sai_Maganti
    Contributor II
    3 years ago

    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.

    • RyanDiehl's avatar
      RyanDiehl
      New Contributor III
      3 years ago

      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

  • Sai_Maganti's avatar
    Sai_Maganti
    Contributor II
    3 years ago

    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

  • RyanDiehl's avatar
    RyanDiehl
    New Contributor III
    3 years ago

    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