topic Re: People Accumulation in a Restaurant in QlikView

People Accumulation in a Restaurant

cristian_av — Tue, 03 Feb 2015 20:51:53 GMT

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)

Accumulated:

Entries by minute:

Hope somebody can help me!

Re: People Accumulation in a Restaurant

sujeetsingh — Tue, 03 Feb 2015 21:01:48 GMT

Try using the in-built accumulation in Expressions

Re: People Accumulation in a Restaurant

Tue, 03 Feb 2015 21:12:11 GMT

Hello,

try this.

for Date, use the function Floor.

Julien

Re: People Accumulation in a Restaurant

Tue, 03 Feb 2015 21:55:55 GMT

Hi Cristian,

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.

Regards,

Bartek

Re: People Accumulation in a Restaurant

rwunderlich — Tue, 03 Feb 2015 22:20:17 GMT

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]

-Rob

Re: People Accumulation in a Restaurant

MarcoWedel — Tue, 03 Feb 2015 23:09:15 GMT

Hi,

one solution could be:

tabRestVisits:
LOAD CAS_TRANSIT_ID,
    TimeStamp(Floor(TRANSIT_DATE,1/1440),'DD-MM-YYYY hh:mm') as TRANSIT_DATE
FROM [http://community.qlik.com/servlet/JiveServlet/download/706689-147813/Example%20Data.xlsm]
(ooxml, embedded labels, table is Hoja1);

tabCalendar:
LOAD *,
    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
While MinDateTime+(IterNo()-1)/1440<=MaxDateTime;
LOAD Min(TRANSIT_DATE) as MinDateTime,
    Max(TRANSIT_DATE) as MaxDateTime
Resident tabRestVisits;

hope this helps

regards

Matrco

Re: People Accumulation in a Restaurant

MarcoWedel — Tue, 03 Feb 2015 23:42:36 GMT

Hi,

in order to analyze the effect of different mean visit durations you could extend this solution like:

hope this helps

regards

Marco

Re: People Accumulation in a Restaurant

cristian_av — Wed, 04 Feb 2015 18:15:34 GMT

Amazing! That's exactly what I need.

Thank you!

Re: People Accumulation in a Restaurant

cristian_av — Wed, 04 Feb 2015 18:16:16 GMT

Thank you!

That is what I need, but the solution Marco Wedel gave it's even better!

Re: People Accumulation in a Restaurant

MarcoWedel — Wed, 04 Feb 2015 19:11:00 GMT

You're welcome.

Regards

Marco