Hello,
I did the below formula in order to count the number of FTE.
It’s works well except for the internal mobility where it counts 0.
One of the employee concerned by this case is the below employee:
What do i need do to in the formula to take into account the internal mobility ?
Thanks in advance
Best answer by francois
Hi Cedric,
It’s quite interesting that you’re experiencing a side effect of using LASTNONBLANK in a case where you’d actually rather have the LAST value.
If you calculate the Dataload.EntryDate[BY LASTNONBLANK: Dalataload.Employee] and Dataload.ExitDate[BY LASTNONBLANK: Dalataload.Employee] separately (e.g. in a Formula Playground), you’ll see they will both show 31/01/2024 - of which the PRORATA is indeed 0.
If you instead use Dataload.EntryDate[BY LAST: Dalataload.Employee and Dataload.ExitDate[BY LAST: Dalataload.Employee, it will give you resp. 31/01/2024 and blank (empty value) - which is what you could use in your Prorata - though it will only display values for the last Job that employee is taking.
Another alternative would be using Dataload.EntryDate[BY MIN: Dalataload.Employee] for the start date, which should give you the minimal date in the transaction for that employee, which could work - though that won’t work with MAX modifier on the term date because that would ignore the BLANK value.
One option to work around that specific point could be to add a value far in the future e.g. IFBLANK(Dataload.ExitDate, DATEVALUE("2030-12-31", "yyyy-MM-dd")) which would fill a value even though the exit date is blank. You can either add that in a calculated property of your transaction and reference it, or directly use it in the PRORATA formula, though it might make things less legible.
PRORATA(
Month,
IFBLANK(Dataload.EntryDate, DATEVALUE("2010-01-01", "yyyy-MM-dd"))[BY MIN: Dataload.Employee],
IFBLANK(Dataload.ExitDate, DATEVALUE("2030-12-31", "yyyy-MM-dd"))[BY MAX: Dataload.Employee]
)
+12Hi,
You should do the PRORATA() first and then aggregate with the correct BY to the level you want.
I’d have said Sub team but as there is no change here it won’t impact much.
I also suggest following our standard app for this, which takes care of all the staging logic into metrics you can easily use
+13Hi Cedric,
It’s quite interesting that you’re experiencing a side effect of using LASTNONBLANK in a case where you’d actually rather have the LAST value.
If you calculate the Dataload.EntryDate[BY LASTNONBLANK: Dalataload.Employee] and Dataload.ExitDate[BY LASTNONBLANK: Dalataload.Employee] separately (e.g. in a Formula Playground), you’ll see they will both show 31/01/2024 - of which the PRORATA is indeed 0.
If you instead use Dataload.EntryDate[BY LAST: Dalataload.Employee and Dataload.ExitDate[BY LAST: Dalataload.Employee, it will give you resp. 31/01/2024 and blank (empty value) - which is what you could use in your Prorata - though it will only display values for the last Job that employee is taking.
Another alternative would be using Dataload.EntryDate[BY MIN: Dalataload.Employee] for the start date, which should give you the minimal date in the transaction for that employee, which could work - though that won’t work with MAX modifier on the term date because that would ignore the BLANK value.
One option to work around that specific point could be to add a value far in the future e.g. IFBLANK(Dataload.ExitDate, DATEVALUE("2030-12-31", "yyyy-MM-dd")) which would fill a value even though the exit date is blank. You can either add that in a calculated property of your transaction and reference it, or directly use it in the PRORATA formula, though it might make things less legible.
PRORATA(
Month,
IFBLANK(Dataload.EntryDate, DATEVALUE("2010-01-01", "yyyy-MM-dd"))[BY MIN: Dataload.Employee],
IFBLANK(Dataload.ExitDate, DATEVALUE("2030-12-31", "yyyy-MM-dd"))[BY MAX: Dataload.Employee]
)
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
