Skip to main content
O
OlofA
Newcomer
March 7, 2024
Solved

Formula for same period previous year

  • March 7, 2024
  • 3 replies
  • 0 views

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!

Best answer by RobbSalzmann

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

 

3 replies

RobbSalzmann
RobbSalzmannAnswer
Legend
March 7, 2024

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

 

    T
    Thanos_A_cpg
    Newcomer
    March 7, 2024

    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

      O
      OlofAAuthor
      Newcomer
      March 7, 2024

      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
      RobbSalzmann
      Legend
      March 7, 2024

      Apologies!

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

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

       

        O
        OlofAAuthor
        Newcomer
        March 7, 2024

        Works like a charm, thank you so much!

          Terms & ConditionsAccessibility statement