Solved

Allocate values to the following months

  • 30 October 2023
  • 2 replies
  • 100 views
A
Rank
Userlevel 1
Badge +2

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

icon

Best answer by francois 31 October 2023, 00:03

View original

2 replies

francois
Rank
Userlevel 6
Badge +14

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!

François (Pigment)
A
Rank
Userlevel 1
Badge +2

Hello François,

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

Have a great day,

Reply


Ask your question here!

Terms & ConditionsCookie settings