Forum Discussion

OlofA's avatar
OlofA
New Contributor II
10 months ago

Formula for same period previous year

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!

Finance
  • RobbSalzmann's avatar
    RobbSalzmann
    10 months ago

    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

     

  • RobbSalzmann's avatar
    RobbSalzmann
    Valued Contributor II
    10 months ago

    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

     

    • Thanos_A_cpg's avatar
      Thanos_A_cpg
      New Contributor II
      10 months ago

      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

  • OlofA's avatar
    OlofA
    New Contributor II
    10 months ago

    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?

    • RobbSalzmann's avatar
      RobbSalzmann
      Valued Contributor II
      10 months ago

      Apologies!

      I left off a bit:
      api.Pov.Time.MemberId

      Dim currYear As Integer = api.Pov.Time.MemberId