Oracle Transactional Business Intelligence

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Sick absence duration sum issue

Question
2
Views
0
Comments

Hello,

I'm trying to produce an analysis showing total days lost to sickness per quarter for each department.

I'm working in the Workforce Management - Absence Real Time subject area, I have filtered by department and have added time.calendar quarter end date as a column, and assignment absences details.duartion.

When I add person ID/name and per assignment absence identifier and absence start and end date, I can see the individual numbers add up (example below with IDs removed for confidentiality). These duration values add to 19.5, which I've checked using other sources is correct. When I remove person ID, per absence entry identifier, and date start/end, and sum(assignment absences details.duartion) I get 35 for that quarter (which matches assignemnt absences.leave duration). I've tried different Aggregation rules which make no difference.

Please help! Thank you


Department Name

Quarter

Calendar Quarter End Date

Per Absence Entry Identifier

Person ID

Date Start

Date End

Duration

WLM

2025        /2026                  Q1

30/06/2025

04/06/2025 00:00

0

WLM

2025        /2026                  Q1

30/06/2025

12/06/2025 00:00

12/06/2025 23:59

0.5

WLM

2025        /2026                  Q1

30/06/2025

01/04/2025 00:00

01/04/2025 23:59

1

WLM

2025        /2026                  Q1

30/06/2025

07/04/2025 00:00

07/04/2025 23:59

1

WLM

2025        /2026                  Q1

30/06/2025

08/04/2025 00:00

08/04/2025 23:59

1

WLM

2025        /2026                  Q1

30/06/2025

09/04/2025 00:00

09/04/2025 23:59

1

WLM

2025        /2026                  Q1

30/06/2025

21/04/2025 00:00

21/04/2025 23:59

1

WLM

2025        /2026                  Q1

30/06/2025

02/05/2025 00:00

02/05/2025 23:59

1

WLM

2025        /2026                  Q1

30/06/2025

03/06/2025 00:00

03/06/2025 23:59

1

WLM

2025        /2026                  Q1

30/06/2025

09/06/2025 00:00

09/06/2025 23:59

1

WLM

2025        /2026                  Q1

30/06/2025

26/06/2025 00:00

26/06/2025 23:59

1

WLM

2025        /2026                  Q1

30/06/2025

11/05/2025 00:00

13/05/2025 23:59

2

WLM

2025        /2026                  Q1

30/06/2025

03/06/2025 00:00

04/06/2025 23:59

2

WLM

2025        /2026                  Q1

30/06/2025

05/06/2025 00:00

06/06/2025 23:59

2

WLM

2025        /2026                  Q1

30/06/2025

17/06/2025 00:00

18/06/2025 23:59

2

WLM

2025        /2026                  Q1

30/06/2025

19/06/2025 00:00

20/06/2025 23:59

2