1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'Discuss Data Visualizations and Charting' started by REDDOG, Nov 24, 2018.

  1. REDDOG

    REDDOG New Member

    Messages:
    16
    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
  2. vletm

    vletm Excel Ninja

    Messages:
    4,431
    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.
  3. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,671
    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 ?
  4. REDDOG

    REDDOG New Member

    Messages:
    16
    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

    Attached Files:

  5. vletm

    vletm Excel Ninja

    Messages:
    4,431
    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.
  6. REDDOG

    REDDOG New Member

    Messages:
    16
    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
  7. vletm

    vletm Excel Ninja

    Messages:
    4,431
    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!)

    Attached Files:

  8. REDDOG

    REDDOG New Member

    Messages:
    16
    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
  9. vletm

    vletm Excel Ninja

    Messages:
    4,431
    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?
  10. REDDOG

    REDDOG New Member

    Messages:
    16
    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
  11. vletm

    vletm Excel Ninja

    Messages:
    4,431
    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 ... <<

    Attached Files:

    Thomas Kuriakose likes this.
  12. vletm

    vletm Excel Ninja

    Messages:
    4,431
    REDDOG
    with some modifications...

    Attached Files:

    Thomas Kuriakose likes this.
  13. REDDOG

    REDDOG New Member

    Messages:
    16
    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
  14. vletm

    vletm Excel Ninja

    Messages:
    4,431
    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 >
  15. REDDOG

    REDDOG New Member

    Messages:
    16
    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
  16. vletm

    vletm Excel Ninja

    Messages:
    4,431
    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
  17. vletm

    vletm Excel Ninja

    Messages:
    4,431
    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: Nov 26, 2018
  18. REDDOG

    REDDOG New Member

    Messages:
    16
    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
  19. vletm

    vletm Excel Ninja

    Messages:
    4,431
    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.
  20. REDDOG

    REDDOG New Member

    Messages:
    16
    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
  21. vletm

    vletm Excel Ninja

    Messages:
    4,431
    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 ;)
  22. REDDOG

    REDDOG New Member

    Messages:
    16
    Excellent
    From my perspective the ability to add value through graphical innovation is invaluable
    Thanks once again
    REDDOG
  23. vletm

    vletm Excel Ninja

    Messages:
    4,431
    ... still [ RUN/PAUSE ] not good!
    ++ can select number of days/weeks ... can make mess too!

    Attached Files:

  24. REDDOG

    REDDOG New Member

    Messages:
    16
    Thank you for your help, so far

    Regards
    REDDOG
  25. vletm

    vletm Excel Ninja

    Messages:
    4,431
    ... RUN/STOP works
    ... but not so as I would like it work
    ... and there are some more modifications too

    Attached Files:

    Thomas Kuriakose likes this.

Share This Page