Skip to main content
Solved

Prorata for overlap period

  • March 7, 2024
  • 2 replies
  • 224 views
W
Master Trendsetter
Forum|alt.badge.img+8

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

Best answer by Issam Moalla

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

2 replies

Issam Moalla
Employee
Forum|alt.badge.img+11
  • Issam MoallaEmployee
  • Employee
  • Answer
  • March 11, 2024

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
Master Trendsetter
Forum|alt.badge.img+8
  • Weining BenMaster Trendsetter
  • Author
  • Master Trendsetter
  • March 13, 2024

Hi Issam,

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

Regards,

Ask your question here!

Terms & ConditionsCookie settingsAccessibility statement