02-09-2022 11:55 AM
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
02-09-2022 12:33 PM
Are you able to share the formula you are using?
02-09-2022 12:38 PM
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
02-09-2022 12:53 PM
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
02-09-2022 12:36 PM
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.