Hello,
I get a transaction from my ERP with not a specific date by line but a start and a end date
| Employee | Project | Start | End | Hours |
|---|---|---|---|---|
| Emp001 | Proj1 | 01/01/2024 | 05/01/2025 | 5 |
| Emp001 | Proj2 | 02/01/2024 | 04/01/2024 | 2 |
I need to create a metric with Employee/Project/Day dimensions in which for each dates between the start and end I put the number of hours
| 01/01/2024 | 02/01/2024 | 03/01/2024 | 04/01/2024 | 05/01/2024 | ||
|---|---|---|---|---|---|---|
| Emp001 | Proj1 | 5 | 5 | 5 | 5 | 5 |
| Emp001 | Proj2 | 2 | 2 | 2 |
Could you explain me the best way to do it ?
Hi,
You can isolate the time frame using the start and end dates, and even consider only the working days.
if(
Day >= timedim(transaction.Start, Day)
and
Day <= timedim(transaction.End, Day)
and
Day.'Day of Week'.'Working days'
, transaction.Hours
)
[by sum: transaction.Employee, transaction.Project]
The TIMEDIM function transforms your dates into Day dimension to isolate the time frame.
The Working days boolean filters Days only on working days.
Then you return the Hours by suming them for each day of the time frame, dimensioned by Employee and Project.
Hope this helps.
Best,
Super thanks a lot, as I suspected it, it was easy :)
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.