Formula to calculate daily workload for resources

[slawomirp]

Hello community

I`m looking for a formula which will visualize daily/weekly/monthly workload for my resources

Scenario:

Let`s say I have 10 machines.
My team has 30 people. Each person can submit a ticket.
Each ticket has a number, start date and finish date ( sometimes it is one day ticket ( easy task ), sometimes it requieres a week of machines work ( complex task ) )
Based on machines workload I am assigning each ticket to each machine.

I would like to check my machines utilization for today / in a week / in a month. Are they all busy today ? ( or is it only 50% ? ). Do I already have a plan for them in a week ? or a month ?

Is there a way to create such a formula ?

Appreciate all the tips.

[morgankejerhag]

Do I understand correctly that the tickets are for whole days? In this case I would create a table for each machine  and day as kind of a transaction table.

Machine, Date, Ticket
M1, 2021-03-01, 0
M1, 2021-03-02, 0
M1, 2021-03-03, 1

Then it is easy to calculate something like

count({<Ticket={1}>} Ticket)/count(Ticket)

[slawomirp]

yes you are correct. Tickets are for whole days.

Can you please clarify what you mean by "and a days a kind of transaction table" ?

How can I check if for today/in a week M1 has a task assigned ?

Just to clarify. If a ticket is assigned to machine then in my excel file field "Machine" is filled with M1, M2, M3 etc. Otherwise it stays empty.

So my excel looks as below

Ticket no, Start Date, Finish Date, Machine

1, 2021-03-01, 2021-03-25,M1
2, 2021-03-04, 2021-03-11,
3, 2021-03-07, 2021-03-8,M2
4, 2021-03-02, 2021-03-25,M4
5, 2021-03-05, 2021-0-6,

[morgankejerhag]

I see! Hm, this would require some calculations to get the percentages correct. I would calculate a complete list of dates for the machine (from the initial point or when it was introduced until today/yesterday or the date when a machine was removed) and intervalmatch that with the table you have where Machine has a value (using date from the generated list, start, finish and Mahcine as a key).

So something like

Data:
Load
   Machine,
   date(MachineAddDate+IterNo()) as Date,
   MachineAddDate,
   MachineRemoveDate
from Machines.qvd (qvd) while MachineAddDate+IterNo()<=MachineRemoveDate;

ActiveIntervals:
Load
   Machine,
   StartDate,
   FinishDate,
   TicketNo
from Tickets.qvd (qvd);

left join (Data)
intervalmatch (Date, Machine) Load StartDate, FinishDate, Machine resident ActiveIntervals;

left join (Data)
Load * resident ActiveIntervals; // To get TicketNo
drop table ActiveIntervals;