Assistance Required creating a Motion Chart to track activity
- Thread starter REDDOG
- Start date
Hi Vietm
Well done with the improvements much appreciated
One observation -if I look at the daily report for utilisation and transaction per hour the data is not returning what I would expect.
Also can you confirm what is being reported ?
If I look at the data for a specific day from 00:00 - 24:00 in one hour segments
the First hour 00:00 -01:00
if there is nothing on the bay then I would expect to see
0 Transactions
0% Utilisation
This would remain the same until a transactions occurs -
lets say at 03:00 - 05:00
thus
03:00 - Transaction 1
03:00 - Utilisation 50% (if 2 bays available)
This logic would then be refreshed on a hourly basis reflecting the transactions on each bay durign the reported hour
Please be clear - this function works perfectly when the date range is WEEK and reports the utilisation and transactions summary by day.
Thoughts please
Regards
REDDOG
Well done with the improvements much appreciated
One observation -if I look at the daily report for utilisation and transaction per hour the data is not returning what I would expect.
Also can you confirm what is being reported ?
If I look at the data for a specific day from 00:00 - 24:00 in one hour segments
the First hour 00:00 -01:00
if there is nothing on the bay then I would expect to see
0 Transactions
0% Utilisation
This would remain the same until a transactions occurs -
lets say at 03:00 - 05:00
thus
03:00 - Transaction 1
03:00 - Utilisation 50% (if 2 bays available)
This logic would then be refreshed on a hourly basis reflecting the transactions on each bay durign the reported hour
Please be clear - this function works perfectly when the date range is WEEK and reports the utilisation and transactions summary by day.
Thoughts please
Regards
REDDOG
REDDOG
-if I look at the daily report for utilisation and transaction per hour the data is not returning what I would expect.
hmm...? What would You expect?
If I look at the data for a specific day from 00:00 - 24:00 in one hour segments
the First hour 00:00 -01:00
Which days data?
Please, always use data, which I can compare Your notes!
if there is nothing on the bay then...
Yes!
So far, the shortest period (SP) is one hour (with day) or one day (with week).
Both (Utilisation & Transaction) values shows values from the whole SP!
(above photo)
You can see 4 transactions (of 5) in 00:00-01:00 - okay? and
there can also see 3:30 for utilisation - okay?
Utilisation% will be LOW (~2%),
because there are < ALL > Factory Areas in calculations.
OUCH! YES!
if [ Week ] then daily max loading hours are 13hrs,
but if [ DAY ] then hourly mar loading hours are 32minutes.
> If You meant this ... then good ... if something else then what? <
then with [ DAY ] Utilisation% would be much higher .. ~73%!
( I modified above feature in newer version. )
Case (if 2 bays available)
If there would be ONE transactions then
utilisation can be from 0% to 100% as You have asked!
basic ... every second in SP can have one transaction!
and there could be 'as many as possible' transactions per every second.
Utilisation depends used transaction time within the shortest period and max number of bay (2).
Both (day and week) calculations solve with 'SAME FORMULAs'.
>> with [RUN/STOP], those black borders show specific times active transactions.
-if I look at the daily report for utilisation and transaction per hour the data is not returning what I would expect.
hmm...? What would You expect?
If I look at the data for a specific day from 00:00 - 24:00 in one hour segments
the First hour 00:00 -01:00
Which days data?
Please, always use data, which I can compare Your notes!
if there is nothing on the bay then...
Yes!
So far, the shortest period (SP) is one hour (with day) or one day (with week).
Both (Utilisation & Transaction) values shows values from the whole SP!
(above photo)
You can see 4 transactions (of 5) in 00:00-01:00 - okay? and
there can also see 3:30 for utilisation - okay?
Utilisation% will be LOW (~2%),
because there are < ALL > Factory Areas in calculations.
OUCH! YES!
if [ Week ] then daily max loading hours are 13hrs,
but if [ DAY ] then hourly mar loading hours are 32minutes.
> If You meant this ... then good ... if something else then what? <
then with [ DAY ] Utilisation% would be much higher .. ~73%!
( I modified above feature in newer version. )
Case (if 2 bays available)
If there would be ONE transactions then
utilisation can be from 0% to 100% as You have asked!
basic ... every second in SP can have one transaction!
and there could be 'as many as possible' transactions per every second.
Utilisation depends used transaction time within the shortest period and max number of bay (2).
Both (day and week) calculations solve with 'SAME FORMULAs'.
>> with [RUN/STOP], those black borders show specific times active transactions.
-if I look at the daily report for utilisation and transaction per hour the data is not returning what I would expect.
hmm...? What would You expect?
For example in the attached I would expect to see 50% for each SP.
Based on my logic --
Area -= Phase 4
Loading Bays = 2
Daily Utilisation Capacity = 48 hrs ( 2 bays x 24 hours )
SP Actual Transaction Count = 1
SP = 1 hour
Therefore SP Utilisation % = SP Transaction Count/SP Utilisation Capacity
= 1 (Transaction Count)/ (48/24)
= 50%
It is this number I want to highlight in addition to the data labels on the graph if the form of a gauge/speedometer type graphic.
Also am I correct in thinking the number selector above the Week/Day parameter is the total number of Bays available.
Thanks as always
REDDOG
1) You have [2] in cells G1 ... challenge to see that moment
... but I'll try to continue ...
2) You use OLDER VERSION - and - it had interesting values
... and is that data same as I have?
Area -= Phase 4
Loading Bays = 2
Daily Utilisation Capacity = 48 hrs ( 2 bays x 24 hours ) --- yes!
SP Actual Transaction Count = 1 --- BUT: 01-02 & 13-14 there are 2
SP = 1 hour --- BUT: 01-02 = 40min & 13-14 = 47min
As I have written and You have also written:
#15 Reply: 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%
= no matter number of 'SP Actual Transaction Count's ... TIME matters!
>> also (48/24) is for FULL DAY! = for 24hrs --- or how?
>> those above values are for ONE HOUR ( 48/24 >>> 0:32:30 )
the form of a gauge/speedometer type graphic.
hmm.. where? how?
... Do You mean to show 7... 24 gauges somewhere? ... hmm?
and for both values?
ouch! and if You select [ 6 ] from G1
... then there could be much more of those! WOW! WOW! or how?
Also am I correct in thinking the number selector above the Week/Day parameter is the total number of Bays available. = NO!
That number effects number of days/weeks would show! as You could see!
Data-sheet has that which You were thinking; below!
Aren't those like 'FIXED'?
... but I'll try to continue ...
2) You use OLDER VERSION - and - it had interesting values
... and is that data same as I have?
Area -= Phase 4
Loading Bays = 2
Daily Utilisation Capacity = 48 hrs ( 2 bays x 24 hours ) --- yes!
SP Actual Transaction Count = 1 --- BUT: 01-02 & 13-14 there are 2
SP = 1 hour --- BUT: 01-02 = 40min & 13-14 = 47min
As I have written and You have also written:
#15 Reply: 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%
= no matter number of 'SP Actual Transaction Count's ... TIME matters!
>> also (48/24) is for FULL DAY! = for 24hrs --- or how?
>> those above values are for ONE HOUR ( 48/24 >>> 0:32:30 )
the form of a gauge/speedometer type graphic.
hmm.. where? how?
... Do You mean to show 7... 24 gauges somewhere? ... hmm?
and for both values?
ouch! and if You select [ 6 ] from G1
... then there could be much more of those! WOW! WOW! or how?
Also am I correct in thinking the number selector above the Week/Day parameter is the total number of Bays available. = NO!
That number effects number of days/weeks would show! as You could see!
Data-sheet has that which You were thinking; below!
Aren't those like 'FIXED'?
> Terminology gotta be clear soon! <
Thanks for your reply -
Firstly - apologies my mistake regarding the number indicator I can see now that this determines the no of days/weeks in the range
Gauge type indicator would only be required for either
% Daily Utilisation or % Weekly Utilisation
I am still struggling to determine the utilisation calculation.
I am trying to understand the total hourly/daily or weekly utilisation of the AREA.
If we have an area with 2 loading bays the maximum number of hours available for the AREA in a 24 hour period is 48 hours - coorect ? (24 hours per bay)
If there is one transaction only - for example between 08:00- 09:00 and no other activity for the 24 hour period then I would expect to see the following
> > Hourly breakdown - from 00:00 - 08:00 - no transactions 0% utilisation in hourly increments.
> > Transaction 1 - between 08:00 -09:00 - 50% AREA utilisation as Bay 1 is 100% utilised Bay 2 is 0% utilised
>> For 09:00 -24:00 - same as 00:00 - 08:00 - 0% AREA Utilisation as zero activity.
>> AREA activity for Day (00:00 -24:00) is 1 hour out of a total 48 available returns 2% total AREA utilisation for the 24 hour period
Thanks
REDDOG
Firstly - apologies my mistake regarding the number indicator I can see now that this determines the no of days/weeks in the range
Gauge type indicator would only be required for either
% Daily Utilisation or % Weekly Utilisation
I am still struggling to determine the utilisation calculation.
I am trying to understand the total hourly/daily or weekly utilisation of the AREA.
If we have an area with 2 loading bays the maximum number of hours available for the AREA in a 24 hour period is 48 hours - coorect ? (24 hours per bay)
If there is one transaction only - for example between 08:00- 09:00 and no other activity for the 24 hour period then I would expect to see the following
> > Hourly breakdown - from 00:00 - 08:00 - no transactions 0% utilisation in hourly increments.
> > Transaction 1 - between 08:00 -09:00 - 50% AREA utilisation as Bay 1 is 100% utilised Bay 2 is 0% utilised
>> For 09:00 -24:00 - same as 00:00 - 08:00 - 0% AREA Utilisation as zero activity.
>> AREA activity for Day (00:00 -24:00) is 1 hour out of a total 48 available returns 2% total AREA utilisation for the 24 hour period
Thanks
REDDOG
There are some challenges ...
Gauge type indicator would only be required for either
% Daily Utilisation or % Weekly Utilisation
= 7... 24 gauges if [G1] = 1 ... would You make rough visual layout?
(( with line, as now, it would be quicker to see something ... ))
hmm ... Your case ...
seems that You've changed back to hrs/hrs-calculation or how?
eg PHASE 4 and [DAY]... okay!
1hrs loading / 1 transaction between 08-09 ... okay!
>> 00-08, 09-24: both results are 0 ... okay
>> 08-09:
hmm ... now, You have added, that You would like to see 'both bays' values ...?
( DHL has 6bays ... ) That is not possible so far! ... because,
that needs information, WHICH bay has used with that transaction! ... or how?
= 'Transaction%' = 1/2 = 50%
= 'Hours% = 1hrs/2hrs = 50%
if check with [WEEK]
there are still same numbers 1hrs / 1 transaction
= 'Transactions%' = 1/2 = 50%
= 'Hours%' = 1hrs/48hrs = ~2%
Those 'Transaction%'s are tricky ...
because if 2 bays is for use all the time ...
then what would be the most correct 'max use value'?
There could be any number of transactions per hour/day/... (in theory).
... and if there would be typos with recording data then ... OUCH!
Of course, same affect will be with 'Hours%' ...
or how ever those would call.
Even the newest version, would have some unwanted features...
I'll check those with values one-by-one and
I'll send newer version after something has found and changed.
Gauge type indicator would only be required for either
% Daily Utilisation or % Weekly Utilisation
= 7... 24 gauges if [G1] = 1 ... would You make rough visual layout?
(( with line, as now, it would be quicker to see something ... ))
hmm ... Your case ...
seems that You've changed back to hrs/hrs-calculation or how?
eg PHASE 4 and [DAY]... okay!
1hrs loading / 1 transaction between 08-09 ... okay!
>> 00-08, 09-24: both results are 0 ... okay
>> 08-09:
hmm ... now, You have added, that You would like to see 'both bays' values ...?
( DHL has 6bays ... ) That is not possible so far! ... because,
that needs information, WHICH bay has used with that transaction! ... or how?
= 'Transaction%' = 1/2 = 50%
= 'Hours% = 1hrs/2hrs = 50%
if check with [WEEK]
there are still same numbers 1hrs / 1 transaction
= 'Transactions%' = 1/2 = 50%
= 'Hours%' = 1hrs/48hrs = ~2%
Those 'Transaction%'s are tricky ...
because if 2 bays is for use all the time ...
then what would be the most correct 'max use value'?
There could be any number of transactions per hour/day/... (in theory).
... and if there would be typos with recording data then ... OUCH!
Of course, same affect will be with 'Hours%' ...
or how ever those would call.
Even the newest version, would have some unwanted features...
I'll check those with values one-by-one and
I'll send newer version after something has found and changed.
Last edited:
Newer ... better ...
I found few lines which needed to modify... more near 'our wishes'.
I also add one more feature for testing ...
Cell A1 [show/hide] is a switch! with that You can see those
transaction and hours which will make upper charts lines - okay?
Cell A1 have to activate BEFORE make 'calculation'.
'Calculation' means to change/select any of below [buttons]
I also added ONE SAMPLE view of those 'wished gauges'.
You can test it ... by editing B2-value
I found few lines which needed to modify... more near 'our wishes'.
I also add one more feature for testing ...
Cell A1 [show/hide] is a switch! with that You can see those
transaction and hours which will make upper charts lines - okay?
Cell A1 have to activate BEFORE make 'calculation'.
'Calculation' means to change/select any of below [buttons]
I also added ONE SAMPLE view of those 'wished gauges'.
You can test it ... by editing B2-value
Still wondering ... where You would like to get those 24 gauges?
Hey Vietm - apologies I have been offline for a couple of days -
Amazing what a fresh pair of eyes can produce - this certainly is an improvement.
I am aware that because currently the data recording is manual there are a number of incorrect enteries - hence the impression that there are more bays than we physically have.
Can you apply the Utilisation for each Bay on a daily basis in the form of a donut chart ??
Thanks
REDDOG
Amazing what a fresh pair of eyes can produce - this certainly is an improvement.
I am aware that because currently the data recording is manual there are a number of incorrect enteries - hence the impression that there are more bays than we physically have.
Can you apply the Utilisation for each Bay on a daily basis in the form of a donut chart ??
Thanks
REDDOG
How those records has done?
If check those records based 'Transactions' or 'On bay',
then previous graph would look different?
'Blue' was based 'Transactions' and 'multicolor' was based 'On bay'.
I could do almost any kind of chart
... but what would 'donuts' give ... if data is 'something'?
That 'multicolor' is one MY version - how I shared those transactions ...
I keep on coding ... there will be some changes
If check those records based 'Transactions' or 'On bay',
then previous graph would look different?
'Blue' was based 'Transactions' and 'multicolor' was based 'On bay'.
I could do almost any kind of chart
... but what would 'donuts' give ... if data is 'something'?
That 'multicolor' is one MY version - how I shared those transactions ...
I keep on coding ... there will be some changes