Skip to main content
O
OlofA
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

R
RobbSalzmannAnswer
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
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
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?

R
RobbSalzmann
March 7, 2024

Apologies!

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

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

 

O
OlofAAuthor
March 7, 2024

Works like a charm, thank you so much!

Terms & ConditionsAccessibility statement