Forum Discussion

Mustafa_A's avatar
Mustafa_A
Contributor II
3 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 dimensi...
Cube View
LeeB's avatar
LeeB
Contributor II
3 years ago

Are you able to share the formula you are using?

  • Mustafa_A's avatar
    Mustafa_A
    Contributor II
    3 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
      3 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