Forum Discussion

Mustafa_A's avatar
Mustafa_A
Contributor II
4 years ago

How do I create this excel calculation in CV?

Hi All,

I'm creating this report which uses a custom formula, that takes SUM/AVG of past six months, and calculates the Pct value. I tried using Rolling6MonthTotal and Rolling6MonthAvg  view dimension in Denominator/Numerator, but it doesn't work as it should. 

Any ideas on how I can make SUM/AVG the prior 6 month, and run this calculation (highlighted).

 

Thank you for your help.

Best,

 

Mustafa A

 

 

Cube View

4 Replies

  • LeeB's avatar
    LeeB
    Contributor III
    4 years ago

    Are you able to share the formula you are using?

    • Mustafa_A's avatar
      Mustafa_A
      Contributor II
      4 years ago

      This is the dynamic formula I'm using:

       

      Try

      Dim R6SUMRentalRevenue As Decimal = api.Data.GetDataCell("A#NetIncome:U4#[RentalRevenue]:V#Trailing6MonthTotal").CellAmount

      Dim R6AvgFleetTotal As Decimal api.Data.GetDataCell("A#Average_Fleet_Total:V#Trailing6MonthAvg").CellAmount


      Return (R6SUMRentalRevenue / R6AvgFleetTotal ) *2


      Catch ex As Exception
      Return Nothing

      End Try

      • db_pdx's avatar
        db_pdx
        Valued Contributor
        4 years ago

        You shouldn't be returning a decimal; you need to return a DataCell.

        Return api.Data.GetDataCell("Divide(A#NetIncome:U4#[RentalRevenue]:V#Trailing6MonthTotal, A#Average_Fleet_Total:V#Trailing6MonthAvg)*2")

        I'd recommend you review the GetDataCell section of the Member Filters training from the Academy courses: https://onestream.litmos.com/course/1004851

        Cheers,

        -DB

  • db_pdx's avatar
    db_pdx
    Valued Contributor
    4 years ago

    Hmm, a basic GetDataCell should work for this.  It would look like:

    GetDataCell(Divide(A#RentalRevenue:V#Trailing6MonthTotal, A#Fleet:V#Trailing6MonthAvg)*2):Name(YourCalc)

    Italicized should be replaced with correct member names.