03-07-2024 05:31 AM - last edited on 03-07-2024 05:47 AM by JackLacava
I'm trying to write a business rule creating a KPI which returns the value of an account for same period previous year.
I already have a working business rule creating a KPI which returns the value of the current period which has the following formula:
Return api.Data.GetDataCell("A#38999:T#" & api.Pov.Time.Name &"").CellAmount
Where 38999 is the account number for which I want to get values.
What formula should I use to, instead of the current period, get the current period but for the same period previous year?
Many thanks in advance!
Solved! Go to Solution.
03-07-2024 07:10 AM - edited 03-07-2024 08:41 AM
This should do it:
Dim currYear As Integer = api.Pov.Time.MemberId
Dim lastYear As Integer = api.Time.AddYears(currYear, -1)
Dim prevPeriod As String = api.Time.GetNameFromId(lastYear)
Return api.Data.GetDataCell($"A#38999:T#{prevPeriod}").CellAmount
03-07-2024 07:10 AM - edited 03-07-2024 08:41 AM
This should do it:
Dim currYear As Integer = api.Pov.Time.MemberId
Dim lastYear As Integer = api.Time.AddYears(currYear, -1)
Dim prevPeriod As String = api.Time.GetNameFromId(lastYear)
Return api.Data.GetDataCell($"A#38999:T#{prevPeriod}").CellAmount
03-07-2024 11:16 AM
You may be able to use
Return api.Data.GetDataCell("A#38999:T#POVPrior12").CellAmount
This will allow you to get the same results without extra code.
Cheers,
Thanos
03-07-2024 07:46 AM - edited 03-07-2024 07:46 AM
Thank you for replying!
When compiling Business rule to check syntax I get the error message"Value of type 'Member' cannot be converted to 'Integer'." for the following row:
Dim currYear As Integer = api.Pov.Time
Do you know what the problem could be?
03-07-2024 07:47 AM - edited 03-07-2024 07:48 AM
Apologies!
I left off a bit:
api.Pov.Time.MemberId
Dim currYear As Integer = api.Pov.Time.MemberId
03-07-2024 08:38 AM
Works like a charm, thank you so much!