• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Assistance Required creating a Motion Chart to track activity

REDDOG

New Member
Hello everyone, new member and my first post.

I am working in a distribution centre mapping activity flows and movement of vehicles on and off loading bays.
Primary objective is to understand the utilisation of both each loading bay and each trailer.

I have a dataset of transactions detailing:

- transaction number
- Trailer ID
- Loading Bay ID
-Date and Time ON Bay
- Date and Time OFf Bay
- Calculated Total Time

What I want to demonstrate in the format of a motion chart, is the weekly activity by transaction for each loading Bay.
Ie - A moving illustration of activity across any given week for a specific loading Bay.

It is particularly important for me to be able to graphically demonstrate the the activity utilisation of each loading Bay representing two aspects -
1) Arepresentation of time each transaction entered and exited the loading Bay
2) A moving summary of individual movements across the week by time

Appreciate this sounds quite complex, in reality it is an activity tracker by time.

Any suggestions on how to proceed with the creation of a graphical display that will capture the imagination whilst modelling throughput would be greatly appreciated

Many thanks and kind regards

REDDOG
 

vletm

Excel Ninja
REDDOG
As You wrote: I have a dataset of transactions detailing.
then You should upload at least sample file here.
Many things would depend that data
... and also, any of Your visual image, what are You looking for.
 

Hui

Excel Ninja
Staff member
Reddog

Firstly, Welcome to the Chandoo.org Forums

Can you please post a sample file with an example of what you mean by Motion Chart ?
 

REDDOG

New Member
Thank you for the welcome,
To both Hui and Vietm - please find attached dataset which essentially is a log of activity .

There are two objectives I am trying to graphically represent.

1) A summary of daily/weekly activity in terms of utilisation hours for each area.

2) An animation model (maybe a motion chart is not the answer) to demonstrate the activity by hour for each area, based on the movements on/off bay.


I am visualising something that through animation/playback maps the time on and off for each transaction over a pre-defined week.
The detail I need is a visual indicator of each transaction arriving and leaving in the form of an animated play back of each days activity by loading area in a weekly animated graph.

Hope this makes sense - a motion chart is the nearest I have seen but these appear to look at cumulative activity over time in a bubble chart.
What I actually want is the actual daily activity by time for all transactions over a week.

Apologies if this is not descriptive enough.

Many thanks for any suggestions or a completely different view of how this can be achieved.

All feedback will be greatfully received.

Many thanks

REDDOG
 

Attachments

vletm

Excel Ninja
REDDOG
Good that there are enough data ...
I quick check datas and
are eg these rows valid: 123, 192, 342, 351, 359, 578, 528 & 664?
There are four Areas ( if not count 'blank' ) ... is that fixed?

I already tried to ask: any of Your visual image, what are You looking for.
 

REDDOG

New Member
Thank you for your reply

Please disregard any blanks, you are correct 4 fixed areas
Users have entered incorrect date formats in some cells please ignore anything that appears to be spurious.

I can only describe or possibly draw what I am looking for.

Basically horizontal axis time in hourly segments Sun -Sat representing 7 days x 24 hourly sections
Because of the size I want to animate or playback to view the daily activity
I want this to be represented in Gantt format ie: For each transaction a horizontal bar from start to finish ( columns Band C)
This I would have multiple transactions over time for each day
The task is to aminate as a rolling record of the full weeks activity

Sorry if this is not clear I will attach a diagram to assist

Many thanks
REDDOG
 

vletm

Excel Ninja
REDDOG
Something still testing some ideas like:

You can select [Factory Area] and [scale].
By press [Prev] or [Next] graph will show prev/next day/week.
By press [RUN/PAUSE], graph will refresh depends 'previous prev/next'.
> Questions? <
( Do not add/modify any data!)
 

Attachments

REDDOG

New Member
Thank you this is really good.
The only questions I would have are -

1) Is it possible to change the scale - so DAY - is in 1 hour segments from 0 -24 - with WEEK being portrayed as 7 consecutive DAYS

Is it possible to advise how this was created - I am assuming there is a Pivot Table behind the graph ??

Really appreciate your efforts so far - many thanks

REDDOG
 

vletm

Excel Ninja
REDDOG
Yes ... with DAY in 1 hour and WEEK ... days? ... anyway checking later!
Not with Pivot Table, because many timestamps last over midnight and many days.

Now, I'm solving how much times used per periods ... or do You need those?
 

REDDOG

New Member
I understand now, grouping on days in a Pivot Table did not work for me as over lapping timestamps ? Thanks for clarifying.

Periods will generally be day/week/month or cumulative to date - I would like to summerize the following within any period selected -

1) Count of Transactions
2) Sum of Hours Utilised (ie- time within selected period with utilisation)
3) Sum of Hours Non Utilised (ie- time within selected period with no utilisation(no activity)

How can the summary figures be presented within the data in a eye catching format ???

Very many thanks

Best Regards

REDDOG
 

REDDOG

New Member
REDDOG
hmm? ... now You have added also MONTH ... hmm?
Months are challenge, because number of days ... okay? Really need?
1)+2) ... You can verify.
3) How would You calculate that?
What would look 'eye catching format'?
>> For testing ... <<
Hi Vietm,
This is starting to look very good - well done !!!
there a few refinements I would like to make and also some questions

1) In terms of a summary indicator (dial, guage, monitor - whatever is appealling to the audience) I am wanting to display as follows (apologies if I have not been clear in my requirements)

> A visual indicator of the Count of Transactions in the Selected Timescale and Area - for example 5 x 1 transactions per day for 7 days would display

an indicator of 5 if a specific day was selected
15 if a three day range was selected
35 if a week was selected
etc.etc.

> Transaction Utilisation is calculated for each selected date range as
the total number of utilsed hours within the selected perid.

Thus if we had 3 x tranactions of a specific day

10:00- 1200 = 2 hours
14:00 -18:00 = (4 hours)
18:00 - 02:00 (following day) = (6 hours - 2 hours falls in to the following day)

This the logic to be applied for for all time ranges.

> Non Utilisation is calculated by subtracting the total utilised hours from the capacity ( to avoid complexity this would be managed seperatley)

ie: Area Phase 4 has capacity of two loading bays thus tota avaialble hours per 24 hour period is 48 utilisation hours - Utilisation % is calculated by Subtracting Total transaction hours for area from the Capacity and expressing as a percentage.

The problem I encounter is where transactions overlap into another 24 hour period.

Looking at your latest example I am not sure if we are aligned as the summary indicators are only required for the time paramenter selected.

:ie - Transactions /Utilisation/ Non Utilsation - total for each 24 hour period


As per my example for Transaction Utilisation - the same logic applies

Hope this makes sense

Once again very many thanks for your efforts and advice

Regards

John
 

vletm

Excel Ninja
hmm?
Ranges: Now You write ranges; day, 3 days, 7 days, week, 24hrs & 48 hrs.
Now, You can see there hourly and daily values.

ou're use term 'problem' ... there are not any that kind of cases!
... maybe there would be some challenges!

Q: If one day would have 11 transactions between 13:00-14:00.
If You would check with 'week', then You'll see 11 transactions in that day.
Do it give correct image? ... 23hrs free!
If You would check with 'day', then You'll see that truth! ... 23hrs free.
If those 11 transactions would be different times (no overlapping),
then there would be 'only' 13hrs free ...

Your A visual indicator... & Non Utilisation is ...
Could You use those values which You could see from file with visual output?
>> Above, would help You to get more Your needed values! <<


Looking at your latest example I am not sure if we are aligned as the summary indicators are only required for the time paramenter selected.
... total for each 24 hour period

If You have selected 'day' then You could get 'hourly values'
otherwise could get 'daily values'.
If ... You need other ranges, then 'values' would be something else!
Count ~ transactions per hour or day within selected range (day/week)
Hours ~ hours which transaction has last within selected range (day/week)
There are NOT any 'Non Utilsation' values because
... should I know, how to solve those?

ie: Area Phase 4 has capacity of two loading bays ...
Screen Shot 2018-11-26 at 13.23.58.png
Screen Shot 2018-11-26 at 13.25.23.png

Okay, I found on moment then there were three in same time ~~~
... but have I known this 'rule'?
How about others? Should I know, that I could solve something?
> small steps >
 

REDDOG

New Member
Vietm - Firstly thank you for your patience - I applaud your optimism :)

I do apologise if I am confusing matters

In answer to your question 1 -
If one day would have 11 transactions between 13:00-14:00.
If You would check with 'week', then You'll see 11 transactions in that day.
Do it give correct image? ... 23hrs free!
If You would check with 'day', then You'll see that truth! ... 23hrs free.
If those 11 transactions would be different times (no overlapping),
then there would be 'only' 13hrs free ...

A: The number of transactions in any period is purely a measure of activity - it has no relevance to the utilisation calculation.

> Utilisation is calculated as follows - For each area - there are a fixed number of loading bays (this information has been excluded from the dataset )
The total utilisation for the area is THE SUM OF HOURS FOR EACH DAY or DAYS.

In the example 11 transactions between 13:00 - 14:00 = 11 hours

(In reality there are only 2 loading bays so there can never be more than 2 transactions in any given one hour period within a 24 hr window)

That aside, if the Utilisation was 11 hours - the calculation to determine % Utilisation capacity is Capacity (2 bays x 24 hrs = 48 hrs) / Daily Actual Utilised Hours 11 in this instance = 22.91%

The challenge is when there is utilisation overlapping between 2 days

eg - 18:00 - 06:00 - 6 hours Day 1 - 6 hours Day 2

This is where I need a formula to apportion the utilisation into the correct days.

In summary -we are nearly there , however the fact that some transactions are spread over more than one day/s the report becomes mis-aligned.

Is it possible to introduce a formula into the base data that splits the start time and finish time into total hours for each day over the number days -

eg -Transaction x Start 25/11/2018 20:00
End 26/11/2018 10:00

Equates to Total Utilisation of 14 hours

For the purposes of the report I think I need to see

25/11/18 - 4 hours
26/11/18 - 10 hours

In which case everything else then becomes manageable - I hope !!!!


Please let me know your thoughts

Very many thanks

REDDOG
 

vletm

Excel Ninja
Sometimes I have time or interesting to try dig answers ... sometimes not :(
So far, there are still some interesting points to solve ... for me :)

Some comments:
> Utilisation is calculated as follows - For each area - there are a fixed number of loading bays (this information has been excluded from the dataset )
Means that I can solve ONLY Phase 4 - values!
The rest ... I could only guess or do those other way.

(In reality there are only 2 loading bays so there can never be more than 2 transactions in any given one hour period within a 24 hr window)
I work only with Your data which You've given and as I showed 'in reality' = three transactions in same time.

My 11hr sample - for me there were two ways to solve one of You value.
You seemed to solve that 'Your way'. I can use 11/48 -solution.
It would tell something ... and it would be quicker to solve.

The challenge is when there is utilisation overlapping between 2 days ...
It's not a challenge ... there are much longer periods too!
Where do You need formula? ... if You can get those values?

... that some transactions are spread over more than one day/s ...
mis-aligned ... hmm? what?
If transaction is from 23:59 to 00:01
then both days get ONE hit and ONE minute
as well as both hours get ONE hit and ONE minute
(depends which range has selected)

Did You comment about:
1) Sum of Hours Utilised ... Sum of Hours Non Utilised ... %

You have 'Sum of Hours Utilised' = Hours
Where You need 'Sum of Hours Non Utilised'?
if You would like to see
'Sum of Hours Utilised / Sum of Hours Non Utilised'
'Sum of Hours Non Utilised' is that X * 24hrs.
X's value is 2 with 'Phase 4' ... others ... as written something?

2) Do You need those any number of days/ weeks and so on for ranges?

3) RUN/PAUSE would work other way ... let's see

... I'll continue testing

I need proper answers
that You would get something
You would like to have
 

vletm

Excel Ninja
There should be below:
Screen Shot 2018-11-26 at 20.39.27.png
(now, some of those would be sample values)
I modified 'hours' to show utilisation%
(with 'week' it would show 'bigger' daily values as You named)
Screen Shot 2018-11-26 at 20.39.56.png
... red is now named as transactions

Also, gotta change direction of chart ... for automation ... now it would 'RUN'!

>> I noticed that You opened new case >>
Do You need this or not?
 
Last edited:

REDDOG

New Member
Thank you for your great work - I have been studying your responses and I think I am understanding the range selection and how this changes the results.

To summerise

There are 4 areas

CPW - 3 Bays - Daily Utilisation Capacity 24 x 3 = 72 hrs
DHL - 6 Bays - Daily Utilisation Capacity 24 x 6 = 144 hrs
PHASE 4 -- 2 Bays - Daily Utilisation Capacity 24 x 2 = 48 hrs
BAY 12 --- 2 Bays - Daily Utilisation Capacity 24 x 2 = 48 hrs

Any other time ranges are and extension of the above -

ie - CPW Weekly Capacity = 72 x 7 = 504 hrs


If I am understanding you correctly the weekly report fixes my problem of utilisation hours across multiple days - is this correct ???

The other point was the transaction count which is determined by two criteria.

1) The number of bays for each area - ie: there cannot be anymore transactions than the number of bays at the same time period.

ie- if there are two bays available and two transactions between 10:00-11:00 for example then there cannot be any more transactions at that time, this is the first restriction.

2) The second restriction is the total number of availble hours which in this example is 48 hours per day (24 hours per bay) in whatever combination of transactions and hours that make up a total of 24 hours.


In terms of the other case - If there is a formula for calculating hours per day
where there is an overlap over different days I would like to know how to do this as part of some other work I am engaged in.


ie: Start time 27/11/18 01:00
Finish Time 28/11/18 02:00

Is 25 hours elapesed time but I want to see 23 hours (27/11) and 2 hours (28/11)


Many thanks for your brilliant efforts

Regards
REDDOG
 

vletm

Excel Ninja
REDDOG
Max loading per Bays .. finally.

If I am understanding you correctly the weekly report fixes my problem of utilisation hours across multiple days - is this correct ???
If You mean that with 'Week' selection You'll get daily values then 'Yes'.

1) There are already over 'max loading per bay' -cases! I can know only data!
If those times are real times - then something not match.
If those times are plan times - then ... someone should check plan!
2) Someway same as above. Some transactions 'stays' in bay for loooooog time!
= if some transactions has forgotten to bay ... or times no match then OUCH!

In terms of the other case - If there is a formula for calculating hours per day
where there is an overlap over different days I would like to know how to do this as part of some other work I am engaged in.

As I answered to Your other case - YES ... and NO!
In this case You have over 800rows data and even one row has times which overlaps more than one night (max 6,5 days)!
Those row has over 100 days data.
Formula ... as You could do it manually on paper
t_tot = 0
if 'start' and 'end' are in same day then

t = 'end' - 'start'
t_tot = t_tot + t
endif
if 'start' and 'end' are in different days then

t = 'next day' - 'start'
t_tot = t_tot + t
'start' = 'next day'
endif
repeat those two ifs as many times needed (eg it transaction lasts 6,5 days)

That my previous uploaded file still needs modifications!

Questions need answers; without answers, it'll be a challenge.
 

REDDOG

New Member
Thank you again,
It’s phsically impossible to be over capacity in terms of utilisation
As100% indicates all bays are utilised 24 hours per day.

Part of the objective is to identify spurious data as I cannot guarantee the integrity of the data
due to user input errors.

Regards
REDDOG
 

vletm

Excel Ninja
Data should be possible to record correct - of course mistakes could happen!
... and this way solving those values, it will give this way results.
= it's a challenge to get high % without long transactions.
> Those transactions can also have own factory area colors as below. <
Screen Shot 2018-11-27 at 12.33.01.png
... and many more features ;)
 

REDDOG

New Member
Excellent
From my perspective the ability to add value through graphical innovation is invaluable
Thanks once again
REDDOG
 
Top