Repeating First or Last Value in Blank Periods / Use of Previous On

  • 28 October 2022
  • 3 replies
  • 367 views
Bill Rausch
Rank
Badge

There have been a few instances where we need to take the fist value of a metric and have it populate earlier periods that are blank and take the last value and populate remaining blank periods.

Lets assume we have a metric called Salary, with a set of values from a limited period (i.e., Mar - Jun):

Jan Feb Mar Apr May Jun Jul Aug
    5000 5000 5250 5250    


Below is the final result we need :

Jan Feb Mar Apr May Jun Jul Aug
5000 5000 5000 5000 5250 5250 5250 5250

 

I’ve seen the use of the previous function to populate the earlier periods:

if(isnotblank(Salary), Salary, previous(Month on -rank(Month.’Start Date’))

and a slightly adjusted formula to populate the later periods (i.e., take out the - in front of the rank function): 

if(isnotblank(Salary), Salary, previous(Month on rank(Month.’Start Date’))

I’ve been unable to find documentation on how the previous function uses the on parameter.

Is there another way to accomplish this or can someone share how the on parameter in the previous function works?

3 replies

S
Rank

Hello Bill,

About your formula, I’d say what you want is:

IFBLANK(
  IFBLANK(Salary, Previous(Month)),
  Salary[REMOVE FIRSTNONBLANK: Month]
)

 I cannot see a simple way to do that using the `ON` operator within the PREVIOUS function.

S
Rank

Also, to give you more context about the `ON` keyword, it helps you override the natural order of the list used in the `PREVIOUS` function.

For instance in you exemple, the list used in your `PREVIOUS` is month, so the natural order is
Jan, Feb, Mar, Apr, May, Jun, Jul, Aug

So, let’s say you have a single amont in Jun, and your formula is `PREVIOUS(Month)`, Jul will report the value of Jun, and Aug will report the value of Jul. The other one will report blank value as the previous month did define no value.

Now if you for instance write `PREVIOUS(Month ON Month.Name)`, the order will be alphabetical on the Month.Name property:
Apr, Aug, Feb, Jan, Jul, Jun, Mar, May

So with that formula, the Mar will report the value of Jun and May will report the value of Mar. The other month will report no value as the previous month (according to the alphabetical order) did define no value.

Ben A
Rank
Userlevel 2
Badge +3

Actually you could use “on” if you break it into several metric but it’s longer

 

 

Reply


Ask your question here!

Terms & ConditionsCookie settings