Solved

Prorata for overlap period

  • 7 March 2024
  • 2 replies
  • 74 views
W
Rank
Userlevel 1
Badge +4

Hello,

We import our sick leave period data into a transaction list in Pigment:

Each employee could have several records of different periods.

But it's possible to have overlap periods for one person, for exemple: Emp_2 has an overlap period from 03/04/2023 - 03/08/2023. So for this case, I have two needs:

  • 1. Create a metric to affiche those employees who have overlap period
  • 2. Even it's error and we warn it, we still want to calculate these sick leave period from the earliest start date to the lastest end date.

 

PS: I’m not sure if it’s helpful to create unique ID for all records in the transaction list. We can do it if need.

 

Thanks you in advance for any advice!!

icon

Best answer by Issam Moalla 11 March 2024, 22:54

View original

2 replies

Issam Moalla
Rank
Userlevel 5
Badge +9

Hi @Weining Ben ,

To achieve what you are looking for, I have added a column ID in the transaction list generated automatically:
 

This column is converted in an additional property to a dimension ID100 to use it in the formulas:
 


Based on the above I created a Leave Aggregation metric which will result in True on the leave days:

'Long Leave'.'Start date'[BY LAST: 'Long Leave'.Employee, 'Long Leave'.ID100] <= Day.'Start Date'
AND
'Long Leave'.'End Date'[BY LAST: 'Long Leave'.Employee, 'Long Leave'.ID100] >= Day.'End Date'


For the Leave Control I have applied the following formula:

IF('Leave aggregation'
[REMOVE ALL: ID100]
[REMOVE ANY: Day]
,True)

and finally for the Sick leave by Employee, a similar formula:

IF('Leave aggregation'
[REMOVE ANY: ID100], TRUE)

The IF(condition,True) is added to set to blanks the false cells.

Hope this helps,
Issam

W
Rank
Userlevel 1
Badge +4

Hi Issam,

Thanks a lot, It’s exactly what I’m looking for! I greatly appreciate for your help! :)

Regards,

Reply


Ask your question here!

Terms & ConditionsCookie settings