Skip to main content

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!!

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'tBY LAST: 'Long Leave'.Employee, 'Long Leave'.ID100] <= Day.'Start Date'
AND
'Long Leave'.'End Date'tBY 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

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