Skip to main content
Solved

Allocate values to the following months


Forum|alt.badge.img+1

Hello to all Pigment enthusiasts,

To estimate my number of Headcounts over the coming months (Forecast), I'm taking the Headcounts on an HR board (here with Position ID with Contract) that you see on the lines in this metric.
(Dimensions: Position ID, Month, Period Type)
 

I'd like to be able to push the values over the following months. Concretely, even if the person (PO 312) is recruited in March 2024, he must be present in all the following months and therefore should have 1 in April 2024, May 2024…

Many thanks for your help

Best answer by francois

Hi!

There are two options you can explore - using either PREVIOUS or PRORATA

PREVIOUS is a function that can be used to “push” the data. In your example, you could use it in combination with IFBLANK and type a formula like this 

IFBLANK(JobOpening.FTE[FILTER: XX][BY COUNT: YY], PREVIOUS(Month))

This means if you have a value, take the value, if not take the previous month’s value.

 

I prefer working with PRORATA though, especially on larger datasets as it tends to compute faster (which becomes handy if you have millions of rows, or a very large calendar).

So instead of JobOpening.FTE[FILTER: XX][BY COUNT: JobOpening.'Position ID', JobOpening.'Arrival Date (Dimension)'], you can type

PRORATA(JobOpening.FTE[FILTER: XX], JobOpening.'Arrival Date')[BY COUNT: JobOpening.'Position ID']

By providing only a start date for the prorata, it will be continued until the calendar ends. Be mindful you have to provide a date and not a dimension like in the BY.

 

Hope this helps!

View original
Did this topic help you find an answer to your question?

francois
Employee
Forum|alt.badge.img+13
  • Employee
  • October 30, 2023

Hi!

There are two options you can explore - using either PREVIOUS or PRORATA

PREVIOUS is a function that can be used to “push” the data. In your example, you could use it in combination with IFBLANK and type a formula like this 

IFBLANK(JobOpening.FTE[FILTER: XX][BY COUNT: YY], PREVIOUS(Month))

This means if you have a value, take the value, if not take the previous month’s value.

 

I prefer working with PRORATA though, especially on larger datasets as it tends to compute faster (which becomes handy if you have millions of rows, or a very large calendar).

So instead of JobOpening.FTE[FILTER: XX][BY COUNT: JobOpening.'Position ID', JobOpening.'Arrival Date (Dimension)'], you can type

PRORATA(JobOpening.FTE[FILTER: XX], JobOpening.'Arrival Date')[BY COUNT: JobOpening.'Position ID']

By providing only a start date for the prorata, it will be continued until the calendar ends. Be mindful you have to provide a date and not a dimension like in the BY.

 

Hope this helps!


Forum|alt.badge.img+1
  • Budding Pigmenteer
  • October 31, 2023

Hello François,

Many thanks for this, it works perfectly!!!!

Have a great day,


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings