I have hundreds of records. Each one represents the time when somebody gets into a restaurant.
I need to know how many people is inside the place by minute.
As I don't when people goes out the restaurant, according to manual timing, people remains inside for about 45 minutes.
So I need to get the accumulate of the count of people for 45 minutes back.
I've tryied using "accumulated 45 steps back" in the graph, but the dimensions are repeated, so I have multiple times the same HH:mm
i.e.: 00:12, 00:12, 00:12, 00:12, 00:13, 00:13 ....
I should get a graphs like this: (Made with VBA/Excel)
Entries by minute:
Hope somebody can help me!
Solved! Go to Solution.
Please see the attached file - is this what you are looking for?
To avoid multiplying dimension values I converted them to text (this is done inside script).
I also created "Client" column to count clients easier - now I can just SUM this column (Sum is faster than Count, so whenever you can better use Sum).
In the chart "Properties -> Expressions" I changed expression and turned on Accumulate 45 steps back. On "Presentation" tab I checked "Enable X-Axis Scrollbar".
I hope it will be helpful for you.
I think it's preferable to round() the hh:mm values, rather than text(). This allow for using a continuous axis if desired.
time(round(frac(TRANSIT_DATE),(1/1440)),'hh:mm') as [HH:mm]
one solution could be:
TimeStamp(Floor(TRANSIT_DATE,1/1440),'DD-MM-YYYY hh:mm') as TRANSIT_DATE
(ooxml, embedded labels, table is Hoja1);
Time(Floor(Frac(TRANSIT_DATE),1/1440),'hh:mm') as [HH:mm],
Minute(TRANSIT_DATE) as Minute,
Hour(TRANSIT_DATE) as Hour,
Day(TRANSIT_DATE) as Day,
WeekDay(TRANSIT_DATE) as WeekDay,
Week(TRANSIT_DATE) as Week,
WeekName(TRANSIT_DATE) as WeekName,
Month(TRANSIT_DATE) as Month,
MonthName(TRANSIT_DATE) as MonthName,
Dual('Q'&Ceil(Month(TRANSIT_DATE)/3),Ceil(Month(TRANSIT_DATE)/3)) as Quarter,
QuarterName(TRANSIT_DATE) as QuarterName,
Year(TRANSIT_DATE) as Year,
WeekYear(TRANSIT_DATE) as WeekYear;
LOAD Timestamp(MinDateTime+(IterNo()-1)/1440) as TRANSIT_DATE
LOAD Min(TRANSIT_DATE) as MinDateTime,
Max(TRANSIT_DATE) as MaxDateTime
hope this helps