Forum Discussion

BenMannes's avatar
BenMannes
New Contributor
10 months ago

cube view column calculation

In my cube view, I need a column (% of net sales) to calculate each row/divided by net sales. In the selected column I am trying to only solve for actual. For example actual gross sales/actual net sales, and then actual sales rebates/actual net sales, and so on down my P&L. There must be a way but I have not found anything useful. I'd greatly appreciate any help.

 

Cube View
  • db_pdx's avatar
    db_pdx
    10 months ago

    Hi BenMannes: you're on the right track, but just slightly off with your GetDataCell.  You'll want to divide the column Actual, by the intersection of [Net Sales]-Actual.  The below assumes the row [Net Sales] generates only 1 result, and the column Actual generates only 1 result

    GetDataCell(Divide(CVC(Actual), CVRC([Net Sales], 1, Actual, 1))):Name(% of Net Sales)

     That said, if you have multiple reports that need to show % of Net Sales consider putting it into a DynamicCalc helper, that way you just append the helper to your member filter (:U8#PercentOfNetSales, for example) and you can avoid the complex GetDataCell math.

    Cheers,    -db

  • db_pdx's avatar
    db_pdx
    Valued Contributor
    10 months ago

    Hi BenMannes: you're on the right track, but just slightly off with your GetDataCell.  You'll want to divide the column Actual, by the intersection of [Net Sales]-Actual.  The below assumes the row [Net Sales] generates only 1 result, and the column Actual generates only 1 result

    GetDataCell(Divide(CVC(Actual), CVRC([Net Sales], 1, Actual, 1))):Name(% of Net Sales)

     That said, if you have multiple reports that need to show % of Net Sales consider putting it into a DynamicCalc helper, that way you just append the helper to your member filter (:U8#PercentOfNetSales, for example) and you can avoid the complex GetDataCell math.

    Cheers,    -db

    • BenMannes's avatar
      BenMannes
      New Contributor
      10 months ago

      That is perfect. Thanks so much for the help on this!

      Ben

  • T_Kress's avatar
    T_Kress
    Contributor III
    10 months ago

    I believe you should write a dynamic calculation in U8 dim instead of doing CV math.  Write it as follows:

    Dim acct As Member = api.Pov.Account

If (Not ViewMember.IsAnnotationTypeViewId(api.Pov.View.MemberId)) Then
     Return api.data.GetDataCell("Divide(A#[" + acct.Name + "]:U8#None,A#NetRev:U2#Top:U8#None)")  
End If

Return Nothing

    Then pull U8#PctofSales in that column on the report.