Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
cristian_av
Contributor III

People Accumulation in a Restaurant

Hi

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!

1 Solution

Accepted Solutions

Re: People Accumulation in a Restaurant

Hi,

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

QlikCommunity_Thread_150637_Pic6.JPG

QlikCommunity_Thread_150637_Pic5.JPG

QlikCommunity_Thread_150637_Pic7.JPG

QlikCommunity_Thread_150637_Pic8.JPG

hope this helps

regards

Marco

9 Replies
sujeetsingh
Honored Contributor III

Re: People Accumulation in a Restaurant

Try using the in-built accumulation in Expressions

Not applicable

Re: People Accumulation in a Restaurant

Hello,

try this.

for Date, use the function Floor.

Julien

Not applicable

Re: People Accumulation in a Restaurant

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

MVP & Luminary
MVP & Luminary

Re: People Accumulation in a Restaurant

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

Hi,

one solution could be:

QlikCommunity_Thread_150637_Pic1.JPG

QlikCommunity_Thread_150637_Pic2.JPG

QlikCommunity_Thread_150637_Pic3.JPG

QlikCommunity_Thread_150637_Pic4.JPG

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

Hi,

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

QlikCommunity_Thread_150637_Pic6.JPG

QlikCommunity_Thread_150637_Pic5.JPG

QlikCommunity_Thread_150637_Pic7.JPG

QlikCommunity_Thread_150637_Pic8.JPG

hope this helps

regards

Marco

cristian_av
Contributor III

Re: People Accumulation in a Restaurant

Amazing! That's exactly what I need.

Thank you!

cristian_av
Contributor III

Re: People Accumulation in a Restaurant

Thank you!

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

Re: People Accumulation in a Restaurant

You're welcome.

Regards

Marco